SQL Server 6.5 on Windows 10 x64

SQL Server 6.5 running on Windows 10

In the same effort as getting SQL Server 4.21a running on Windows 10, I found that SQL Server 6.5 will run as well.  For what it’s worth, SQL Server 6.0 runs, but the enterprise manger will not run, giving this fun error:

sdf

The SQLOLE OLE object could not be registered.

And SQL 7.0 just bombs out with this:

x

Your SQL Server installation is either corrupt or has been tampered with (unknown package id).

Which clearly means I’m missing something in trying to transplant settings.  However for some reason SQL 6.5 I can register the SQLOLE type, and boom!

SQL 6.5 in action

SQL Server 6.5 running on Windows 10

SQL Server 6.5 running on Windows 10

On Win64 vs Win32 and COM objects

I should mention that when registering a COM object you typically run something like this:

regsvr32.exe \mssql\binn\SQLOLE65.DLL

Which picks up the one in the default path.  What about system32?

%SYSTEMROOT%\system32\regsvr32.exe \mssql\binn\SQLOLE65.DLL

Well it turns out that this ‘system32’ directory is actually the 64bit system directory!  And attempting to do this will just result in the error:

64bit regsvr32 on a 32bit COM object

64bit regsvr32 on a 32bit COM object

The module was loaded but the call to DllRegisterServer failed with the code 0x80040005. Well great.  This typically goes back to a permissions issue, or the wrong regsvr32.exe being called.

However on a Win64 based OS, you actually need to specify the Win32 version of regsvr32 which actually lives in the SysWOW64 directory, and run the command prompt at administrator!  So you would run it like this:

%SYSTEMROOT%\SysWOW64\regsvr32.exe \mssql\binn\SQLOLE65.DLL

And you should get:

adf

32bit regsvr32 working

With this COM object registered, you can now launch the Enterprise manager!

Also I found a semi fun way to rename the SQL server:

sp_configure ‘allow updates’, 1
go
reconfigure with override
go
delete sysservers
go
sp_addserver YOURSERVERNAME,local
go
shutdown
go

Running this and it renamed the local SQL instance, and shut it down.  Restarting and it connected to itself just fine.  Naturally change YOURSERVERNAME to whatever your hostname is.  SQL server always wants to be called whatever the actual hostname is, otherwise things break in strange and confusing ways.

Thoughts

Is this terribly useful?  Probably not.  But I think it’s kind of interesting to run 90’s era server software in the 21st century.  Sure I wouldn’t want to run any of it in any type of production environment, but it shows at it’s core how Win32 has not drifted.  However looking at the Microsoft Management console of SQL Server 7.0, and how it will not either run on Windows 10, nor will the snapin run show just how fragile the house of COM turned out to be, and meanwhile good old fashioned Sybase/Win32  code still runs from 1993 onward.

I suppose the next thing to do is to try it on Wine, or a fun enough debugger/syscall trace to see what on earth SQL 7.0’s problem is.  I don’t have any doubt that it’s nothing that can’t be fixed, although back to the root point, would you really want SQL 7.0 in 2016… or even SQL 2000 for that matter.

SQL Server 4.21a on Windows 10 x64

SQL Server 4.21a!

SQL Server 4.21a!

It’s been 7 YEARS, since I last took the SQL Server 4.21a plunge by getting it running on Windows Vista.  I was thinking with all this Windows NT 3.1 fun, I should get SQL Server 4.21a up and running on my current Windows 10 machine.  However this proved more involved.

Unlike Windows Vista, the setupdll.dll from Windows NT 4.0 will not work.  I used the one from Windows NT 3.1, and it will run the setup program fine, but the file copy bombs out.  I went crazy and modified the setup.inf to not actually copy files, take an xcopy of the raw files from an old install, and it won’t even try to install the service, it just builds a master database, and exits.

Trying to run the SQL Server directly, and you get this fun error:

16/10/13 21:19:08.40 kernel   Unable to start due to invalid serial number.

Well isn’t that great.  So naturally you either have to install it, or just import an existing registry key setup like this SQL.REG file.

If you are so inclined, you can even remove named pipe support, and have it listen only on TCP/IP.  Or the other way around.  Or even change the TCP port.

In the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQLServer\Server key there should be a Multi-String key called ListenOn … and it’s just the network transport DLL, and how they listen.  As you can see SSNMPNTW is the named pipe transport, and SSMSSOCN is the TCP/IP transport.

ListenOn key

ListenOn key

However it’s not terribly useful as it turns out that master database is actually empty.  So without stored procedures or much of anything you really can’t do anything with it.  However looking at the install directory there is a bunch of SQL scripts.  Even better on the VM where I’ve installed it, there is some output files, that by their date & time tell me in what order to run them!

First things first, I copy the following files from the C:\SQL\DLL diredctory to the C:\SQL\BINN directory so I don’t have to mess with paths:

NTWDBLIB.DLL
OPENDSNT.DLL
SQLEVENT.DLL
SQLTL32.DLL
SSNMPNTW.DLL
SSMSSOCN.DLL

Now I can run the SQL server from the C:\SQL\BINN directory

START SQLSERVR.EXE

And now it’s running.  If there is any issues, or your master database is either damaged, or just plain doesn’t exist, you can create one with the BLDMASTR.EXE program.

C:\sql\BINN>BLDMASTR.EXE
Buildmaster 4.20a NT : Wed Jan 26 12:37:00 1994

Master disk name? (default is master.dat) :

Master disk size (in 2k blocks)? (default is 6144) :

Configuration only? (default is N) (y or n) : n

Databases only? (default is N) (y or n) : n
Master device: master.dat
writing configuration area
writing the MASTER database
writing the MODEL database
writing allocation pages for remaining 7 MB, (3584 pages)
7 MB
Buildmaster complete

It’s that simple!  Move the master.dat file into the C:\SQL\DATA directory and if the server doesn’t find it by itself, you can just tell it where it’s located:

START SQLSERVR.EXE -d ..\DATA\master.dat

And it should start.

Now the file CONFIG.SQL needs to be modifed (if you have a prior config) or created.

The two key lines are:

update master.dbo.sysdevices set phyname=’C:\SQL\DATA\MASTER.DAT’ where name = ‘master’
sp_addserver YOURMACHINE,local

Which as you can imagine simply sets where the master database lives, and what the machine name is, as SQL server LOVES to know the correct machine name.  With that done, here is my simple script for populating the master database:

CHARSET.EXE /S . CP437\noaccent.437
isql -Usa -P < ..\INSTALL\INSTMSTR.SQL
isql -Usa -P < ..\INSTALL\INSTNT.SQL
isql -Usa -P < ..\INSTALL\INSTMODL.SQL
isql -Usa -P < ..\INSTALL\INSTCAT.SQL
isql -Usa -P < ..\INSTALL\CONFIG.SQL
isql -Usa -P < ..\INSTALL\INSTPUBS.SQL
isql -Usa -P < ..\INSTALL\ADMIN2.SQL
isql -Usa -P < ..\INSTALL\OBJECT2.SQL
isql -Usa -P < ..\INSTALL\HELPSQL.SQL

And once this has finished it will have populated all the tables in your master database.  Hit CTRL+C in the SQL Server window, and it’ll shut down. Re-launch it, and it’ll be initialized.

Assuming this all went according to plan, you can now launch SQLADMIN, the SQL Administrator, and then you can get a connect screen like this (I only got it running with the named pipes transport…)

SQL Admin connect

SQL Admin connect

Remember by default the sa user has no password!

SQL Administrator

SQL Administrator

And we are good to go!  Feel free to grab SP4, and apply it by unzipping, and copying the files & dll’s to their respective places.  So there we go, from January of 1994, to October of 2016, SQL Server still running!

ReactOS hits 0.4!

So as a totally unfair test, I thought I’d load SQL server 6.5 on it.

SQL Server 6.5 on ReactOS

SQL Server 6.5 on ReactOS

After copying in some NT 4.0 dll’s regarding licensing it’ll start to install but the service itself doesn’t work.

bummer.

MSDE2000 didn’t work either.

And the setup program from SQL 4.21 just crashed.

Running MS SQL Server 4.21 on OS X.

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

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.

 

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….

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

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

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!