pink cylinders

More than Four

...there's an axis for that.

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!