Windows 10, .NET 3.5, and TLS 1.2

One thing about Windows 10 is that even though they're all called Windows 10, each major update are sufficiently different that things can break. I rolled out Windows 10, version 1909 to all users a few weeks ago, and sure enough, something broke, maybe.

On the very first working day of 2020 at 8:30 a.m., users called in to say the program they use to upload exchange rates doesn't work and returns a cryptic error message: The underlying connection was closed: An unexpected error occured on a send.

The program is actually very simple, it connects to the Bank of Thailand's website and simply downloads the current exchange rate in Excel format. It then parses the Excel file and ports the exchange rate data into the ERP system. The error seems to suggest the file doesn't exist or there's some other connectivity error. The Bank of Thailand changed their website layout once many years ago, but I seriously doubted they would do this on the very first day of the year. Sure enough, the website still exists and a manual download of the file worked fine, so the issue must be elsewhere.

I asked the users when they started having the issue, and they said they've been having it a while, but since we were busy with end of the year stock taking and moving servers, they decided to do manual entry instead of reporting the issue. Well that sucks.

A Google search for the error message suggests that it's due to newer operating systems defaulting to TLS 1.2, but I know that the program worked fine in Windows 7, and we rolled out Windows 10 quite a while ago, and it worked until recently, and since the users weren't sure when they started having the issue, I wasn't sure if 1909 was the problem.

However, TLS sounded right since the program was developed using Visual Studio 2008 with .NET 3.5. Many solution I found on the net were impossible to do since they suggest modifying the code with SecurityProtocolType.Tls12, but my version of Visual Studio doesn't have such an option. A lot of the solutions also suggest upgrading to .NET 4.8 which is also impossible. Finally, after about three hours of searching and trying all kinds of different things I managed to find an easy to implement solution. The surprising thing about this solution is that it doesn't involve registry editing or installing a hotfix, which is much easier for me since I have the source code. I guess most of the other solutions try to modify the OS since most legacy apps probably don't have the source code available any more.

Fortunately, after the modification the program worked fine and the users are happy again. Unfortunately the program no longers works in Windows 7, but we're phasing Windows 7 out any way so I decided not to modify the program to work with both Windows 10 and Windows 7.

Throwing hardware at the problem

My ERP project had been live for a few months, but I never managed to resolve the slowness issue. I'm not very good with SQL Server, and all the optimizations I did probably had limited effects on the performance since Dynamics NAV's C/SIDE code is executed client side and the idiots I have for consultants don't know the difference between native database and SQL Server. I couldn't get to the ERP's code, and even if I could, I probably wouldn't want to hack the code anyway.

Few months ago my boss suddenly told me that we made huge profits this month, and to avoid paying huge amounts of tax on the huge profits, he's giving me special permission to buy stuff. A lot of stuff.

Old server specs (purchased in October 2007): Intel Core 2 Quad Q600 (2.4 GHz), 8 GB DDR2 RAM, ASUS P5K Premium, and 2x500 GB SATA (RAID1). (Was 3x500 GB SATA RAID5, but I listened to the consultants and changed the RAID5 to RAID1, with no difference in speed whatsoever.)

New server specs (purchased in late 2010):

Oh wait...

New server specs: Intel Core i7-950 (3.06 GHz), 24 GB DDR3 RAM, ASUS P6X58D Premium, and 2x160 GB SSD (RAID1).

Windows 7's experience index score is 7.5 on the CPU and RAM, and 7.9 on the hard drive. I wanted to buy a faster CPU, but the 950 is most cost effective, and RAM is maximized for Core i7.

Oh, and the ERP didn't run any faster than before. None whatsoever.

I've talked about my Core 2 Quad Q6600 servers many times before. They were originally bought to replace the older domain controllers and run the ERP database. But I used them for SQL only due to performance recommendations, not that it made any difference since my database is so small. Now that I have the Core i7 dedicated to SQL, I decided to replace domain controllers with the Q6600 servers, and also upgrade the entire domain to 2008 R2 functional level.

Windows Server 911

What could be worse than never performing backups? Server crashes in the middle of the backup process. Which was exactly what happened to me a few days ago, while I was backing up SQL data off our ERP server, and then the work day started and users started calling in the complain.

After the server rebooted, the Intel ICH RAID started rebuilding itself and was stuck at 33% and the ECC errors kept on climbing, which indicated a bad hard drive. The Intel console didn't say which drive was going bad though. Fortunately, the BIOS settings page did say that drive 0 was bad, so I replaced the drive with an identical one.

Unforunately, after the drive was replaced, it restarted the RAID building process, but the Intel Matrix Storage Console also complained that there's a missing drive. I kept wondering if I did something wrong, but luckily, after about 30 hours of RAID rebuilding, the process completed and everything was fine, and the missing drive error disappeared. (I didn't capture an image of the missing drive error, since I thought it would be there even after the rebuild was complete.)

So everything turned out to be fine. But during the 30 hours that the RAID was rebuilding, I took another full backup of the SQL data and restored it into my server's identical twin with the newer OS. So now I have two identical servers with different OS's and different versions of SQL Server.

The older server is Windows Server 2008 with SQL Server 2008, and the new, live, server is Windows Server 2008 R2 and SQL Server 2008 R2. After some testing, I decided to just take out the older server and replace it with the new one, since I've been wanting to upgrade to R2 for a while. So the drive crashing and stressful server rescue (and getting abused by the users) turned out to be a good thing after all.

Before:

After:

Oh, and what could be more stressful than bringing a new server with restored databases online? A database went missing. But this was a result of the bad information our ERP consultants gave me (i.e. I should always restore database backups using the ERP client and not using SQL Server), which further proves their uselessness.

The accidental DBA

A lot of DBA seem to be accidents. A search for accidental DBA on Google turns out millions of matches, and now I'm one of them.

A few months ago, when my Microsoft Dynamics NAV ERP project was about to go live, one of the last things we got stuck at was a process called adjust cost item entries ("ACIE"), which basically takes all the transactions you've made, and figures out the average or FIFO cost of a particular item in your inventory.

So I was looking at the adjust cost process, and it was taking a long time. A loooong time. A looooooooong time. A looooooooooooooooooong time. After a few hours, I killed the process and decided to ask the consultant about it.

The mor^H^H^Hconsultant said something like... your server is probably way below our recommended specs, adjust cost only takes two hours on her notebook, you need to buy a new server, or install a modern OS like Windows Server 2003 and SQL Server 2005. I told her that our server already runs Windows Server 2008 SP1 and SQL Server 2008. Both are 64-bit. The server has a Core 2 Quad Q6600, 8 GB RAM, and loads of disk space in a RAID5 configuration. She then mumbled something about Windows Server 2008 is too new and probably incompatible with our hardware, but then she suddenly jumped and said that RAID5 is baaaaad for SQL Server.

Hmmf. I do know that RAID5 is not a very good choice to run SQL Server on, and our server really wasn't all that powerful in 2010 (it was bought in late 2007), but then the server was specifically bought to run the ERP software, our database wasn't that big, and we only have 20 users. Even if it's sub-optimal, it shouldn't take forever to do something that needed to be done weekly or even daily.

After trying a whole bunch of settings on both the hardware and on Windows, and being nagged to no end by the consultants that my RAID5 setup is no good for running SQL Server and that I need to buy the Enterprise Edition of SQL Server instead of the Standard Edition, I finally brought my second server with identical specs online, but I installed the database files on separate drives. It's not RAID10 like they asked, but it has separate drives for the OS, for SQL Server itself, for the database files, for the log, and tempdb. I also installed Windows Server 2008 R2 and SQL Server 2008 Enterprise.

So I started the adjust cost batch process on this server, and exactly 24 hours later, it completed! I had never been able to complete the adjust cost process on the live server, since the database server can't accept posting entries while ACIE is running. But running for 24 hours wasn't exactly acceptable. (I could even accept 12 hours (!) since our working hours is 12 hours.)

I couldn't understand why the process only takes two hours on her notebook, so I decided to steal a look at the notebook when she wasn't looking to see how things were configured there. But then I discovered that she was running the native client version of the Dynamics NAV software. GAH!

Dynamics NAV comes in two different versions. The native version uses a file based database, and the SQL Server client version which, of course, uses SQL Server. They're very similar, but code has to be optimized differently depending on which version is used. I realized then that the idi^H^H^Hconsultants had absolutely no clue whatsoever about SQL Server, and decided to take matters into my own hands. (Okay, I had an idea earlier that they were clueless when they installed the entire database into my boot drive when the data drive had 1 TB of empty space, but they argued that I set up the drives wrong.)

Oh, I did get the ACIE issue resolved. While I was trying out different hardware settings, one of their other consultants (PURE GENIUS, by God!) mumbled something about bugs in the ACIE routine, and sent me an object, which immediately resolved the issue, and my ACIE time dropped from 24 hours to minutes. Proving that all the problems had nothing to do with my hardware whatsoever.

Since I was taking matters into my own hands (becoming the accidental DBA), I had to start learning first. I first went to Amazon and bought The NAV/SQL Performance Field Guide, which is targated at Dynamics NAV and Professional SQL Server 2008 Internals and Troubleshooting, which is targated at SQL Server in general. Unfortunately, since I was such a newbie at SQL, and I like Wrox books in general, so I also got Beginning Microsoft SQL Server 2008 Administration. (Since then, I've also bought Manning's SQL Server 2008 Administration in Action, which is by far my favorite right now.)

After burying myself in the books for a few weeks, I started working on the SQL Server. First, I discovered that the consultants had allocated 40 GB to the NAV database's PRIMARY file, and it was only using 60 MB. The data filegroup, however, was set to auto-grow and it was at 12 GB with 99% full. So I resized it into a more logical 128 MB PRIMARY and 16 GB data. They told me that I had allocated too much space when they were the ones that did the idiotic allocation.

Second, I discovered that the NAV log file was 200 GB. We've never done a backup using SQL Server, since the consultant insisted that we should backup only using the Dynamics NAV client and not through SQL Server. I backed up the log which cleared it out, and resized it to a more logical 8 GB. Since we only do daily backups and don't care about data loss during the day, I also decided to change the recovery mode to simple.

After the above, I made maintenance plans to make daily backups using SQL Server's backup function. Backing up using the NAV client by then took nearly an hour, and restoring the database took nearly three hours. Backing up and restoring using SQL Server only took about 5 minutes.

Next, The Dynamics NAV/SQL Performance Field Guide provided an index defragmentation script, which took more than an hour to complete on the first run. I guess it was an indication how fragmented my database was. Nowadays it takes about 2-3 minutes to run.

The consultants also said that my server has way too little RAM (it has 8 GB) since task manager indicates that SQL is using all the memory. 8 GB is not that much for SQL Server, but we have a very small database! Besides, SQL Server is designed to cache data in memory and it's normal for SQL Server to use large chunks of memory. But I still made changes to SQL Server so it doesn't automatically take up all the memory.

I wrote above that the ACIE issue was resolved. However, the ERP software was still suffering performance problems, and the assw^H^H^H^Hconsultants were still insisting that my RAID5 (and lack of RAM) was causing problems. By this time I've given up explaining to them patiently that my RAID5 had nothing to do with the sluggishness, since the ACIE problem was fixed by a simple object. (Anyway, just by pure chance, or maybe it was the Will of the Gods, one of the drives in the RAID5 array died and I ended up in a RAID1 configuration, which I describe in another post.)

After doing all that, the ERP software is still uselessly slow most of the time. What do I do in times like these? Why, throw more hardware at the problem, of course.

(Image source: Imagine-Net Technology)

PSD_DISABLEPRINTER

I mentioned going back to Windows Server 2003 R2 earlier, and here I'll talk about why. While testing our Dynamics NAV ERP software under Windows 7 and Windows Server 2008 R2's Remote Desktop Services, I discovered one serious issue.

The "Printer..." button was missing from the Page Setup dialog. The printer selection button is very important since we have different types of pre-printed forms pre-loaded into different dot matrix printers. Since the paper size has to be preset into the printer form page, without the printer selection button, there's absolutely no way we could get custom sized forms to print. As usual, our hopeless ERP implementors were no help, since they have no experience with anything newer than Windows XP. ("Use XP" was what they said.)

After a lot of searching, I discovered here that the printer selection button was deemed obsolete from Windows Vista onwards. There are various workarounds and code samples posted at various places to deal with the problem. However, since I wans't writing my own application but was running Microsoft's own ERP software, the missing button turned out to be critically important, and I was forced to go back to Windows Server 2003 R2.

The Page Setup dialog from Windows XP.



Page Setup dialog from Windows Server 2008 R2. Note the missing "Printer..." button in the lower right corner.


The margins and stuff are different since they're captured from different computers.

All I got was this cool jacket

I talked about our flailing ERP project earlier. Stupidly enough, half a year has passed since that post, and the project is still getting nowhere.

I did get an invitation from Microsoft to join the Dynamics | NAV Customer Connection event that took place earlier this week. At the event, Microsoft representatives explained features of the new Dynamics NAV 2009 version. Which was pretty sad for me to hear, since we paid for NAV 4.0 (I still have the unopened installation CD), then upgraded to 5.0 during the implementation process, and now 2009 is on the horizon, and we still haven't gone live yet.

Of course, I took the opportunity to vent to the Microsoft representatives about how hopeless my NAV partner is. They appeared to take this matter seriously as they took copious notes, and promised to call me back. But the best part about all this is that they gave me a Dynamics Team jacket for my troubles. So at least now I have something to show for spending $100,000 on the ERP project.

Not a destination

I've long been a fan of William Stanek's technical books. (Can one really be a "fan" of technical books?) Unfortunately, I've never actually owned any of his books until not long ago, only recommended them to others.

Late last year, my company embarked on a huge and expensive project to replace our aging ERP system with a new one, so I decided to take the opportunity to also replace our slightly aging servers running Windows 2000 Server with new servers with Windows Server 2003 R2 x64. I didn't actually have any extensive hand-on experiences with Server 2003 at the time, so I took the opportunity and ordered Windows Server 2003 Administrator's Companion (2nd Edition) and Stanek's Windows Server 2003 Administrator's Pocket Consultant (2nd Edition). The bookstore only had the 1st Edition of Stanek's book, so I placed an order and it took them three months to get the 2nd Edition in.

One word about the aging ERP system. We've been using it for years. It's running on the Windows 2000 Server, but it was moved from the older servers running Windows NT Server 4.0. It never worked properly inside Windows 2000 since I think it was really designed for LAN Manager, and the client only runs inside Windows 98. Both the server side application and the client side software are full of bugs and require constant maintenance. Our users have to run VMware Player to run Windows 98 to access the ERP software. It's a piece of crap that's long overdue for an upgrade. The ERP system was also the reason I stuck with Windows 2000 Server. It was a pain to get working with 2000, and it didn't work with x64 at all. (But the reason this is so is because we never upgraded to a newer version that supported modern operating systems.)

Actually, when spec'ing out the new servers, I wanted to go with the then newly announced Windows Server 2008 (previously known as Windows Server Codename "Longhorn"), but since we already had unused licenses for Windows Server 2003, my boss didn't want to spend any more money for licenses, plus 2008 wasn't actually released yet at the time, so I figured 2003 R2 was good enough, and we could upgrade to 2008 in the future, like when SP1 comes out or something.

Unfortunately, our ERP project got incredibly delayed due to unforseeable circumstances, and the new servers I put together for the ERP software were just sitting there gathering dust while I studied the documentation and did test runs. In hindsight, I should have put the servers into production use right then, but I wanted to start fresh all at once, but of course I didn't realize at the time how much the ERP project would be delayed.

Fast forward to last month, and my dust gathering servers silently passed their 1st birthday, still unused, and suddenly my boss walked into my office and told me to go ahead and present a budget to him to upgrade to Windows Server 2008.

My unused Windows Server 2003 licenses and books! Eventhough I've studied the books extensively, it's not like they were actually put into any kind of "production use". But of course I took the opportunity this time to buy Stanek's Windows Server 2008 Administrator's Pocket Consultant, as well as put in an order for Windows Server 2008 Inside Out.

2,000 more pages to digest...