pink cylinders

More than Four

...there's an axis for that.

SQL Server 2017 on Linux Mint

I followed the instructions as laid out here:

My target was a VM instance of Linux Mint 18.3, called "golem". Everything just works... SQL Server is running automatically after I boot the VM.

Some other links I need to review:

 

Interestingly, the database properties don't seem to know about the Linux host:

image

Fart-ing forward slashes

I have a home-grown database build script that allows me to build multiple instances of my database.

The build scripts allow me to target a number of different environments (test, dev, etc) and perform upgrade or replace operations.

I use the wonderful FART utility to perform text substitution into templated script files, using placeholders delimited with @@.  (FART is a command-line utility developed by Lionello Lunesu. The name is an acronym for "Find-And-Replace-Text". )

For example, consider the following template script, build.tpl:

create database @@DB_NAME@@
containment = partial
on primary
( name = '@@DB_NAME@@',
filename = '@@DATA_PATH@@@@DB_NAME@@.mdf',
size = 5120KB,
filegrowth = 1024KB
)
log on
( name = '@@DB_NAME@@_log',
filename = '@@LOG_PATH@@@@DB_NAME@@_log.ldf',
size = 1024KB,
filegrowth = 10%
)
collate Latin1_General_CS_AS;
go

This template can be written out as a targeted script using a sequence of command-line instructions:

copy build.tpl build.sql

set SB_DB_NAME=MY_NEW_DB
set SB_DATA_PATH=c:\SQL\Data\
set SB_LOG_PATH=C:\SQL\Log\

fart *.sql @@DB_NAME@@ %SB_DB_NAME%
fart *.sql @@DATA_PATH@@ %SB_DATA_PATH%
fart *.sql @@LOG_PATH@@ %SB_LOG_PATH%

Running this yields the following output:

build.sql
Replaced 5 occurence(s) in 1 file(s).

build.sql
Replaced 1 occurence(s) in 1 file(s).

build.sql
Replaced 1 occurence(s) in 1 file(s).

And the contents of build.sql is now:

create database MY_NEW_DB
containment = partial
on primary
( name = 'MY_NEW_DB',
filename = 'C:\SQL\Data\MY_NEW_DB.mdf',
size = 5120KB,
filegrowth = 1024KB
)
log on
( name = 'MY_NEW_DB_log',
filename = 'C:\SQL\Log\MY_NEW_DB_log.ldf',
size = 1024KB,
filegrowth = 10%
)
collate Latin1_General_CS_AS;
go

This is now ready to execute as part of the larger automated build process (This is obviously a simplified example.)

This is all fine and dandy until we try to target a brand new instance of SQL Server 2017 running on Linux. (Distribution of choice: Mint). The paths need to be changed to the unix-style with forward slashes:

set SB_DB_NAME=MY_NEW_DB
set SB_DATA_PATH=/var/opt/mssql/data/
set SB_LOG_PATH=/var/opt/mssql/data/

You'd think this would Just Work, but unfortunately we get an error:

build.sql
Replaced 5 occurence(s) in 1 file(s).

> fart *.sql @@DATA_PATH@@ %SB_DATA_PATH%
Error: invalid option -/
Error: invalid option -o
Error: invalid option -t
Error: invalid option -/
Error: invalid option -m
Error: invalid option -l
Error: invalid option -/
Error: invalid option -d
Error: invalid option -t
Error: invalid option -/

We have to escape the slashes, and also tell FART to use c-style extended characters, with the -C switch:

set SB_DB_NAME=MY_NEW_DB
set SB_DATA_PATH=\/var\/opt\/mssql\/data\/
set SB_LOG_PATH=\/var\/opt\/mssql\/data\/

fart *.sql @@DB_NAME@@ %SB_DB_NAME%
fart -C *.sql @@DATA_PATH@@ %SB_DATA_PATH%
fart -C *.sql @@LOG_PATH@@ %SB_LOG_PATH%

This looks a bit odd, and the output to console includes a warning message:
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
build.sql
Replaced 1 occurence(s) in 1 file(s).

Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
build.sql
Replaced 1 occurence(s) in 1 file(s).

However, the substitution did take place, and the result is correct:

create database MY_NEW_DB
containment = partial
on primary
( name = 'MY_NEW_DB',
filename = '/var/opt/mssql/data/MY_NEW_DB.mdf',
size = 5120KB,
filegrowth = 1024KB
)

etc.

This  console warning message is probably a bug in FART.

UPDATE: And someone else has already reported it: #12 Warning: unrecognized character escape sequence: \/.

Virtuabox Host Networking revisited

My current work environment consists of a Windows 10 host running Virtualbox 5.2 with a Windows 7 guest VM,in which I run my client development tools.

I had Virtualbox Host Networking set up on the default 192.168.56.x subnet, but the second adapter in the guest wasn’t configured because at the time I had no need to “see” the host resources from the guest.

Until now…   I find that I needed to point some client tools on the guest VM at the SQL Server instance running on the host.

Okay, no problem, just set up the second adaptor on the guest as 192.168.56.56, set the gateway as 56.1, and it should all work, right?

image

Yeah, well, for some reason it did not work. I could PING the guest from the host side, but the host was not responding to pings from the guest.

Temporarily disabling the Windows Firewall on the host allowed the guest to “see” the host. So it was a Windows 10 host firewall configuration issue.

I found this post that explains how best to address this problem:

https://superuser.com/questions/936581/how-to-config-windows-firewall-so-vm-host-only-can-ping-windows-7

Essentially:

  • Open Windows Firewall
  • Scroll the right-hand pane down until you see “Windows Firewall Properties”
  • Click on it
  • Select Public Profile
  • Click on “Customize”
  • un-check the “Virtualbox Host Only Network”

 

image

This will disable Windows Firewall from getting in the way of networking between the host and guest.

Thanks, Andrew Joe!

Upgrading site from BlogEngine 2.5 to 3.3

I though this was going to be a nightmare, but getting the content moved from 2.5 to 3.3 was the easiest part, using the BlogML export and import. The tricky part was adapting the Standard theme to match my previously customized one. A straight copy of the theme folder did not work.

This will do for now.

Flyway DB

For years I've used a database build script framework written in-house to recompile all schema objects and run unit tests. However, the actual DDL code to migrate a schema from one version to the next was always left as a hand-rolled script that is executed up-front before the normal build process.

A colleague introduced me to Flyway DB and I am now a fan. The introduction, documentation, and tutorial material is top-notch.

Check it out here: https://flywaydb.org/

Although it has an API for integration, I specifically like the command-line utility version:  https://flywaydb.org/getstarted/firststeps/commandline

Recommended.

Testing using SQL and common table expressions

Sometimes I forget how to do simple things, like splitting a string into sections based on a delimiter.

Here's a nice way to test your code on a range of input values, and see the interim results, in one hit:

with 
myobject as (
   -- put in a range of test values:
   select 'dbo.MyFunction' as NAME
   union
   select 'setup.ins_Contact'
   union
   select 'MyOtherFunction'
),
more2 as (
   -- calculate interesting attributes:
   select
      NAME,
      len( NAME )            as LENGTH_,
      charindex( '.', NAME ) as POSITION
   from myobject
)
-- perform the test:
select 
   NAME, POSITION, LENGTH_,
   case when POSITION=0 then 'dbo'
                        else substring( NAME, 1, POSITION-1 ) 
                        end as SCHEMA_,
   substring( NAME, POSITION+1 , (LENGTH_ - POSITION ) ) as OBJECT_
from more2;
;

iMac; Linux; GRUB; OS X: Can't we all just get along?

I'm not really a fan of OS X. I'm sure the underlying OS is fine - after all, It's a Unix system. I know this! - but the Finder; the keyboard layout; the relentless updates; but most of all the fuzzy text aggravated me.

After some success with a Bootable USB drive containing a 64-bit Linux Mint ISO, I decided to follow Clem's excellent instructions here: 

http://community.linuxmint.com/tutorial/view/1643

and install Linux Mint 17.1 on my 2013 iMac.

Now, I'm not crazy: I resized the primary OS X partition and divided up the remaining space, just as Clem recommended:

/dev/sda4    30 GB   ext4
/dev/sda5     4 GB   swap
/dev/sda6   500 GB   ext4 

After the Linux install completed, I also followed the "Fixing the boot order" instructions, about installing efibootmgr and making the EFI boot Linux first. Supposedly this was to achieve:

"The boot order should now indicate that it will run Mint first, and if that ever came to fail.. it would then run Mac OS. In other words our MacBook now boots into Grub. From there we can select Mint or press Escape and type "exit" to boot into Mac (we'll fix the Mac grub entries to make it exit without having to type anything later on in this tutorial)."

I'm not sure what I did wrong... maybe nothing. Maybe we're just expected to have a complete understanding of EFI and GRUB and bootloader configuration in general, at this point.

Anyway, bottom line: I never saw GRUB, or a menu of boot options, or anything. It just booted straight into Linux. Fortunately the Mac HD volume is visible and accessible under Linux, and I've been able to copy my files over as the need arises, from inside Linux Mint. Excellent.

But what if I want to execute a Mac OS X application natively, for some reason? Dammit, I want to dual boot this thing. The normal Option- key (Mac Boot menu) no longer works, and neither does Option-R (to get to the Mac Recovery partition).

After some research, I learned that pressing 'c' during the boot process gets us to the GRUB command line. Now I could use the ls -l command to list the partitions:

hd2,gpt1      fat efi
hd2,gpt2      hfsplus   'Mac HD'
hd2,gpt3      hfsplus   'Recovery HD'
hd2,gpt4      ext *                       29 GiB
hd2,gpt5      -                            5 GiB
hd2,gpt6      ext *                      438 GiB

but this still left me with no understand of how to boot into one of them. More research:

http://askubuntu.com/questions/16042/how-to-get-to-the-grub-menu-at-boot-time

OK, so I learn about GRUB 2 and the significance of the /etc/grub.d scripts and the /etc/default/grub file, and commenting out the GRUB_HIDDEN_TIMEOUT=0 setting by prefixing it with '#'.
 
Dedoimedo has a really nice tutorial on GRUB here:
 
That taught me how any changes to these grub config files should be followed up with:
 
$ sudo update-grub
 
After that, when I restart, I get the boot menu! Yay. From the partition list (see above) I'm pretty sure that I should be able to add a menu option for the OS X partition.
 
More research:

http://askubuntu.com/questions/179689/mac-os-x-wont-boot-from-grub-menu-in-ubuntu-precise-on-apple-mba5-2

It suggests adding the following to the /etc/grub.d/40_custom script:

   menuentry "OS X" {
insmod hfsplus
set root=(hd1,gpt2)
chainloader /System/Library/CoreServices/boot.efi
}

Long story short: that works perfectly. After sudo update-grub and a reboot, we have a menu and an option that will boot into OS X.

Thanks, Internet!

Installing VirtualBox 4.3 on Windows 7-64 - Solved

I was preparing for an Oracle online workshop on Database 12c multi-tenancy, and as part of the prep, you get to download a VM image with the lab environment. Of course, being Oracle, it is a Virtual Box vm.

Now, I like Virtual Box. It works great on my Mac at home, but for some reason I've had problems installing 4.x on my work laptop (Lenovo W520, getting on a bit now but still recommended).

The problem is Virtual Box almost completely installs - until it gets a certain point, then the progress bar runs backwards and it says "rolling back install", followed by:

"Oracle VM VirtualBox 4.3.20 Setup Wizard ended prematurely" because of "an error". This is followed up by "Installation failed! Fatal error during installation.".

No other information provided. Running as Adminstrator after a cold reboot didn't help.

Well... I really wanted to have the Workshop lab environment available, so despite the fact that last time I encountered this problem I didn't find any useful help on the web, I tried again... and this time, I found something on the forums, a post by user Tronmech:

https://forums.virtualbox.org/viewtopic.php?f=6&t=61785

I'll repeat the instructions here in case for some reason that post vanishes:

  • Go into the registry at: HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Network
  • Change "MaxNumFilters" from 8 to 20 (decimal)
  • Reboot
  • Install 4.3.x (run installer as administrator, just in case)

This time, the install was successful. Thanks, Tronmech!

After some more research, it seems that Windows 7 has a maximum hard-coded limit of 14 network filter drivers... and for some reason, this setting in the registry reduces it further to 8, by default.

So setting the MaxNumFilters key to 20 probably only lifts the artificial limit and allows 14 possible filters. Applications such as Virtual Machine managers and VPN clients need to add filters to the network stack, and increasing this limit in the registry seems to be the recommended fix.

In one KB article, I read that a Windows Service Pack could not be installed until some filters were removed!

To view currently deployed Network Filter Drivers, right-click on your connection widget in the Network Connections control panel applet, and view properties:

I believe that each of those checkboxed items is a "Network Filter Driver". I have 9 of them, and I assume that the "VirtualBox Bridged Networking" driver was the 9th one added after I lifted the limit of 8.

Fuzzy Fonts in OS X web browsers

My better half and I recently upgraded our personal computers from aging Sony Vaio laptops, to twin 2013 iMacs. We've wanted to dip our feet in the OS X waters for some time now, and this seemed to be a good opportunity.

I'd always supposed Apple Macs to be a paragon of typography perfection, so imagine my surprise when I discovered the great "fuzzy font" controversy surrounding recent OS X releases.

There's a lot been written about this elsewhere so I won't repeat it:

Anyway, the problem seems to be particularly noticeable when light text on a dark background is used. Unfortunately, this directly affects my Prodigal Sounds family of web sites. 

Thanks to a post by Tim Van Damme, I found a work-around: to add a block to the web site's CSS stylesheet:

html {
-webkit-font-smoothing: antialiased;
}

This seems to force the browser (at least, Safari and Chrome) to work as though the LCD font smoothing option was turned off, and the text is more readable for those browsing on Macs, even if they have the LCD font smoothing option enabled.

If you're on a Mac, you can see the results here at www.prodigalsounds.com.