SQLServer on Ubuntu Restore Tests

Just for fun I decided to test how portable backup files made on the Windows version of SQLServer restore into the Linux version of SQLServer.  I used the wizard built in to my SQL2016 SSMS software connected to the Linux version running on Ubuntu 16 on Amazon EC to run the restore.  The restore was uneventful.  I chose the AdventureWorks2012 database simply because its what I found lying around in the directory I first looked in on my laptop.

The script version of what the wizard generated is as follows…..

USE [master]
RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'C:\home\ubuntu\AdventureWorks2012-Full Database Backup.bak' WITH FILE = 1, MOVE N'AdventureWorks2012_Data' TO N'C:\var\opt\mssql\data\AdventureWorks2012_Data.mdf', MOVE N'AdventureWorks2012_Log' TO N'C:\var\opt\mssql\data\AdventureWorks2012_log.ldf', NOUNLOAD, STATS = 5



Interestingly the tool browsed the directory structure ok but for some reason could not find my backup file, even when saying “all files” and no it wasn’t perms or anything.  I manually typed in the file name to the wizard browser and it worked fine.   I am NOT using the recommended version of SSMS for connecting to Linux on purpose.   I want to find out what breaks if I don’t…. 🙂


SQLServer Running On Ubuntu

So its official.  SQLServer does in fact install on linux and at first pass works.   I just spun up a Ubuntu 16 instance on Amazon EC.   I followed the install steps published by Microsoft and … well … it worked.   See transcript below.

One variance is that rather than sudo and run a command in a single step I sudo’d first then ran the step.  I only run admin tasks when I’m root, not user tasks, this is just how I roll.

Otherwise what follows is boiler plate from Microsoft.

First they setup the repository

root@ip-172-30-0-180:/etc# curl c | apt-key add -
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 983 100 983 0 0 1858 0 --:--:-- --:--:-- --:--:-- 1858
root@ip-172-30-0-180:/etc# curl 04/mssql-server.list | tee /etc/apt/sources.list.d/mssql-server.list
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 87 100 87 0 0 368 0 --:--:-- --:--:-- --:--:-- 370
deb [arch=amd64] xenial main

Then update apt-get so it knows about the new repository….

root@ip-172-30-0-180:/etc# apt-get update
Hit:1 xenial InRelease
Get:2 xenial-updates InRelease [1 02 kB]
Get:3 xenial-backports InRelease [102 kB]
Get:4 xenial/main Sources [868 kB ]
Get:5 xenial/restricted Sources [ 4,808 B]
Get:6 xenial/universe Sources [7, 728 kB]
Get:7 xenial-security InRelease [102 kB]
Get:8 xenial/multiverse Sources [ 179 kB]
Get:9 xenial-updates/main Sources [206 kB]
Get:10 xenial-updates/restricted Sources [1,804 B]
Get:11 xenial-updates/universe So urces [110 kB]
Get:12 xenial-updates/multiverse Sources [3,648 B]
Get:13 xenial-updates/main amd64 Packages [426 kB]
Get:14 xenial-updates/main Transl ation-en [164 kB]
Get:15 xenial-updates/restricted amd64 Packages [6,576 B]
Get:16 xenial-updates/restricted Translation-en [2,016 B]
Get:17 xenial-updates/universe am d64 Packages [365 kB]
Get:18 xenial InRelease [2,828 B]
Get:19 xenial-updates/universe Tr anslation-en [132 kB]
Get:20 xenial-updates/multiverse amd64 Packages [7,384 B]
Get:21 xenial-updates/multiverse Translation-en [2,988 B]
Get:22 xenial-backports/main Sour ces [3,180 B]
Get:23 xenial-backports/universe Sources [1,868 B]
Get:24 xenial-backports/main amd6 4 Packages [4,392 B]
Get:25 xenial-backports/main Tran slation-en [3,104 B]
Get:26 xenial-backports/universe amd64 Packages [2,412 B]
Get:27 xenial-backports/universe Translation-en [1,216 B]
Get:28 xenial/main amd6 4 Packages [940 B]
Get:29 xenial-security/main Sources [49.1 kB]
Get:30 xenial-security/restricted Sources [1,8 04 B]
Get:31 xenial-security/universe Sources [14.4 kB]
Get:32 xenial-security/multiverse Sources [728 B]
Get:33 xenial-security/main amd64 Packages [17 7 kB]
Get:34 xenial-security/main Translation-en [71 .2 kB]
Get:35 xenial-security/restricted amd64 Packag es [6,576 B]
Get:36 xenial-security/restricted Translation- en [2,016 B]
Get:37 xenial-security/universe amd64 Packages [62.6 kB]
Get:38 xenial-security/universe Translation-en [33.9 kB]
Get:39 xenial-security/multiverse amd64 Packag es [2,764 B]
Get:40 xenial-security/multiverse Translation- en [1,124 B]
Fetched 11.0 MB in 1s (6,270 kB/s)
Reading package lists... Done

Next we install the package for SQLServer from Microsoft…..

root@ip-172-30-0-180:/etc# apt-get install -y mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
gcc-5-base gdb gdbserver libbabeltrace-ctf1 libbabeltrace1 libc++1 libc6
libc6-dbg libcc1-0 libcurl3 libjemalloc1 libpython-stdlib
libpython2.7-minimal libpython2.7-stdlib libstdc++6 libunwind8 python
python-minimal python2.7 python2.7-minimal
Suggested packages:
gdb-doc clang glibc-doc python-doc python-tk python2.7-doc binutils
The following NEW packages will be installed:
gdb gdbserver libbabeltrace-ctf1 libbabeltrace1 libc++1 libc6-dbg libcc1-0
libcurl3 libjemalloc1 libpython-stdlib libpython2.7-minimal
libpython2.7-stdlib libunwind8 mssql-server python python-minimal python2.7
The following packages will be upgraded:
gcc-5-base libc6 libstdc++6
3 upgraded, 18 newly installed, 0 to remove and 59 not upgraded.
Need to get 158 MB of archives.
After this operation, 734 MB of additional disk space will be used.
Get:1 xenial-updates/main amd64 l ibpython2.7-minimal amd64 2.7.12-1ubuntu0~16.04.1 [339 kB]
Get:2 xenial-updates/main amd64 p ython2.7-minimal amd64 2.7.12-1ubuntu0~16.04.1 [1,295 kB]
Get:3 xenial/main amd64 python-mi nimal amd64 2.7.11-1 [28.2 kB]
Get:4 xenial-updates/main amd64 l ibc6 amd64 2.23-0ubuntu4 [2,586 kB]
Get:5 xenial-updates/main amd64 l ibpython2.7-stdlib amd64 2.7.12-1ubuntu0~16.04.1 [1,884 kB]
Get:6 xenial-updates/main amd64 p ython2.7 amd64 2.7.12-1ubuntu0~16.04.1 [224 kB]
Get:7 xenial/main amd64 libpython -stdlib amd64 2.7.11-1 [7,656 B]
Get:8 xenial/main amd64 python am d64 2.7.11-1 [137 kB]
Get:9 xenial-updates/main amd64 g cc-5-base amd64 5.4.0-6ubuntu1~16.04.4 [16.8 kB]
Get:10 xenial-updates/main amd64 libstdc++6 amd64 5.4.0-6ubuntu1~16.04.4 [393 kB]
Get:11 xenial/main amd64 libbabel trace1 amd64 1.3.2-1 [34.7 kB]
Get:12 xenial/main amd64 libbabel trace-ctf1 amd64 1.3.2-1 [88.3 kB]
Get:13 xenial-updates/main amd64 gdb amd64 7.11.1-0ubuntu1~16.04 [2,525 kB]
Get:14 xenial-updates/main amd64 gdbserver amd64 7.11.1-0ubuntu1~16.04 [172 kB]
Get:15 xenial-updates/main amd64 libcc1-0 amd64 5.4.0-6ubuntu1~16.04.4 [38.8 kB]
Get:16 xenial-updates/main amd64 libcurl3 amd64 7.47.0-1ubuntu2.2 [186 kB]
Get:17 xenial/main amd64 libunwin d8 amd64 1.1-4.1 [46.5 kB]
Get:18 xenial/universe amd64 libc ++1 amd64 3.7.0-1 [226 kB]
Get:19 xenial-updates/main amd64 libc6-dbg amd64 2.23-0ubuntu4 [3,679 kB]
Get:20 xenial/universe amd64 libj emalloc1 amd64 3.6.0-9ubuntu1 [78.9 kB]
Get:21 xenial/main amd6 4 mssql-server amd64 [144 MB]
Fetched 158 MB in 3s (44.7 MB/s)
Preconfiguring packages ...
Selecting previously unselected package libpython2.7-minimal:amd64.
(Reading database ... 53664 files and directories currently installed.)
Preparing to unpack .../libpython2.7-minimal_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking libpython2.7-minimal:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package python2.7-minimal.
Preparing to unpack .../python2.7-minimal_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking python2.7-minimal (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package python-minimal.
Preparing to unpack .../python-minimal_2.7.11-1_amd64.deb ...
Unpacking python-minimal (2.7.11-1) ...
Preparing to unpack .../libc6_2.23-0ubuntu4_amd64.deb ...
Unpacking libc6:amd64 (2.23-0ubuntu4) over (2.23-0ubuntu3) ...
Setting up libc6:amd64 (2.23-0ubuntu4) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for man-db (2.7.5-1) ...
Selecting previously unselected package libpython2.7-stdlib:amd64.
(Reading database ... 53901 files and directories currently installed.)
Preparing to unpack .../libpython2.7-stdlib_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking libpython2.7-stdlib:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package python2.7.
Preparing to unpack .../python2.7_2.7.12-1ubuntu0~16.04.1_amd64.deb ...
Unpacking python2.7 (2.7.12-1ubuntu0~16.04.1) ...
Selecting previously unselected package libpython-stdlib:amd64.
Preparing to unpack .../libpython-stdlib_2.7.11-1_amd64.deb ...
Unpacking libpython-stdlib:amd64 (2.7.11-1) ...
Processing triggers for mime-support (3.59ubuntu1) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libpython2.7-minimal:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Setting up python2.7-minimal (2.7.12-1ubuntu0~16.04.1) ...
Linking and byte-compiling packages for runtime python2.7...
Setting up python-minimal (2.7.11-1) ...
Selecting previously unselected package python.
(Reading database ... 54410 files and directories currently installed.)
Preparing to unpack .../python_2.7.11-1_amd64.deb ...
Unpacking python (2.7.11-1) ...
Preparing to unpack .../gcc-5-base_5.4.0-6ubuntu1~16.04.4_amd64.deb ...
Unpacking gcc-5-base:amd64 (5.4.0-6ubuntu1~16.04.4) over (5.4.0-6ubuntu1~16.04.2) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up gcc-5-base:amd64 (5.4.0-6ubuntu1~16.04.4) ...
(Reading database ... 54465 files and directories currently installed.)
Preparing to unpack .../libstdc++6_5.4.0-6ubuntu1~16.04.4_amd64.deb ...
Unpacking libstdc++6:amd64 (5.4.0-6ubuntu1~16.04.4) over (5.4.0-6ubuntu1~16.04.2) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Setting up libstdc++6:amd64 (5.4.0-6ubuntu1~16.04.4) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Selecting previously unselected package libbabeltrace1:amd64.
(Reading database ... 54465 files and directories currently installed.)
Preparing to unpack .../libbabeltrace1_1.3.2-1_amd64.deb ...
Unpacking libbabeltrace1:amd64 (1.3.2-1) ...
Selecting previously unselected package libbabeltrace-ctf1:amd64.
Preparing to unpack .../libbabeltrace-ctf1_1.3.2-1_amd64.deb ...
Unpacking libbabeltrace-ctf1:amd64 (1.3.2-1) ...
Selecting previously unselected package gdb.
Preparing to unpack .../gdb_7.11.1-0ubuntu1~16.04_amd64.deb ...
Unpacking gdb (7.11.1-0ubuntu1~16.04) ...
Selecting previously unselected package gdbserver.
Preparing to unpack .../gdbserver_7.11.1-0ubuntu1~16.04_amd64.deb ...
Unpacking gdbserver (7.11.1-0ubuntu1~16.04) ...
Selecting previously unselected package libcc1-0:amd64.
Preparing to unpack .../libcc1-0_5.4.0-6ubuntu1~16.04.4_amd64.deb ...
Unpacking libcc1-0:amd64 (5.4.0-6ubuntu1~16.04.4) ...
Selecting previously unselected package libcurl3:amd64.
Preparing to unpack .../libcurl3_7.47.0-1ubuntu2.2_amd64.deb ...
Unpacking libcurl3:amd64 (7.47.0-1ubuntu2.2) ...
Selecting previously unselected package libunwind8.
Preparing to unpack .../libunwind8_1.1-4.1_amd64.deb ...
Unpacking libunwind8 (1.1-4.1) ...
Selecting previously unselected package libc++1:amd64.
Preparing to unpack .../libc++1_3.7.0-1_amd64.deb ...
Unpacking libc++1:amd64 (3.7.0-1) ...
Selecting previously unselected package libc6-dbg:amd64.
Preparing to unpack .../libc6-dbg_2.23-0ubuntu4_amd64.deb ...
Unpacking libc6-dbg:amd64 (2.23-0ubuntu4) ...
Selecting previously unselected package libjemalloc1.
Preparing to unpack .../libjemalloc1_3.6.0-9ubuntu1_amd64.deb ...
Unpacking libjemalloc1 (3.6.0-9ubuntu1) ...
Selecting previously unselected package mssql-server.
Preparing to unpack .../mssql-server_14.0.1.246-6_amd64.deb ...
Unpacking mssql-server ( ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libpython2.7-stdlib:amd64 (2.7.12-1ubuntu0~16.04.1) ...
Setting up python2.7 (2.7.12-1ubuntu0~16.04.1) ...
Setting up libpython-stdlib:amd64 (2.7.11-1) ...
Setting up python (2.7.11-1) ...
Setting up libbabeltrace1:amd64 (1.3.2-1) ...
Setting up libbabeltrace-ctf1:amd64 (1.3.2-1) ...
Setting up gdb (7.11.1-0ubuntu1~16.04) ...
Setting up gdbserver (7.11.1-0ubuntu1~16.04) ...
Setting up libcc1-0:amd64 (5.4.0-6ubuntu1~16.04.4) ...
Setting up libcurl3:amd64 (7.47.0-1ubuntu2.2) ...
Setting up libunwind8 (1.1-4.1) ...
Setting up libc++1:amd64 (3.7.0-1) ...
Setting up libc6-dbg:amd64 (2.23-0ubuntu4) ...
Setting up libjemalloc1 (3.6.0-9ubuntu1) ...
Setting up mssql-server ( ...

| Please run /opt/mssql/bin/sqlservr-setup to complete the setup of |
| Microsoft(R) SQL Server(R). |

Processing triggers for libc-bin (2.23-0ubuntu3) ...

Next we do a brief setup…. ( ignore my host name resolution failure thats not a problem with microsoft. )

root@ip-172-30-0-180:/tmp# /opt/mssql/bin/sqlservr-setup
Microsoft(R) SQL Server(R) Setup

You can abort setup at anytime by pressing Ctrl-C. Start this program
with the --help option for information about running it in unattended

The license terms for this product can be downloaded from and found
in /usr/share/doc/mssql-server/LICENSE.TXT.

Do you accept the license terms? If so, please type "YES": YES

Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:

Setting system administrator (SA) account password...
sudo: unable to resolve host ip-172-30-0-180

Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y
Created symlink from /etc/systemd/system/ to /lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/ to /lib/systemd/system/mssql-server-telemetry.service.

Setup completed successfully.

Now lets validate its configured and running via systemctl ….

root@ip-172-30-0-180:/tmp# systemctl status mssql-server
● mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabl
Active: active (running) since Mon 2016-11-28 16:43:37 UTC; 23s ago
Main PID: 3888 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─3888 /opt/mssql/bin/sqlservr
└─3902 /opt/mssql/bin/sqlservr

Nov 28 16:43:39 ip-172-30-0-180 sqlservr[3888]: [84B blob data]
Nov 28 16:43:39 ip-172-30-0-180 sqlservr[3888]: [122B blob data]
Nov 28 16:43:39 ip-172-30-0-180 sqlservr[3888]: [145B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [66B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [75B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [96B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [100B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [71B blob data]
Nov 28 16:43:40 ip-172-30-0-180 sqlservr[3888]: [124B blob data]
Nov 28 16:43:44 ip-172-30-0-180 sqlservr[3888]: [315B blob data]

Here is what your processes look like initially under the new mssql user the install created.

root@ip-172-30-0-180:/var/opt/mssql/data# ps -fu mssql
mssql 3888 1 0 16:43 ? 00:00:00 /opt/mssql/bin/sqlservr
mssql 3894 1 0 16:43 ? 00:00:00 /opt/mssql/bin/sqlservr-telemetry /var/opt
mssql 3902 3888 1 16:43 ? 00:01:57 /opt/mssql/bin/sqlservr

The default directory for files is apparently here

root@ip-172-30-0-180:/var/opt/mssql/data# ls -l /var/opt/mssql/data/
total 69696
-rw-r----- 1 mssql mssql 4194304 Nov 28 16:48 master.mdf
-rw-r----- 1 mssql mssql 2097152 Nov 28 16:55 mastlog.ldf
-rw-r----- 1 mssql mssql 8388608 Nov 28 16:53 modellog.ldf
-rw-r----- 1 mssql mssql 8388608 Nov 28 16:53 model.mdf
-rw-r----- 1 mssql mssql 15400960 Nov 28 16:55 msdbdata.mdf
-rw-r----- 1 mssql mssql 786432 Nov 28 16:55 msdblog.ldf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 18:59 tempdb.mdf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 19:00 templog.ldf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 16:57 TESTDB_log.ldf
-rw-rw---- 1 mssql mssql 8388608 Nov 28 16:53 TESTDB.mdf

And the pathing to files is listed as follows inside SSMS when scripting a database create statement.   Notice that it still refers to drive C: but then uses the actual linux filesystem paths.  The C: is therefore meaningless in this context.  More research on what it does with drive letters is needed.

( NAME = N'TESTDB', FILENAME = N'C:\var\opt\mssql\data\TESTDB.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
( NAME = N'TESTDB_log', FILENAME = N'C:\var\opt\mssql\data\TESTDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

Also surprising is the following output for ipcs which does not show any shared memory segments etc owned by the new user mssql.   This is an area to follow up with research.

root@ip-172-30-0-180:/var/opt/mssql/data# ipcs -a

------ Message Queues --------
key msqid owner perms used-bytes messages

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 0 root 644 80 2
0x00000000 32769 root 644 16384 2
0x00000000 65538 root 644 280 2

------ Semaphore Arrays --------
key semid owner perms nsems
0x000000a7 0 root 600 1



Automatic Startup of Postgres 9.4 on SUSE 12

For whatever reason after installing postgres9.4 via zypper on a SUSE 12 instance I had just spun up in the amazon EC cloud postgres would not automatically start at boot time.

The service file was in fact installed

/etc/systemd/system # ls -l /usr/lib/systemd/system/postgresql.service
-r--r--r-- 1 root root 413 Jun 23 22:11 /usr/lib/systemd/system/postgresql.service
/etc/systemd/system #

The contents were


Description=PostgreSQL database server

ExecStart=/usr/lib/postgresql-init start
ExecStop=/usr/lib/postgresql-init stop
ExecReload=/usr/lib/postgresql-init reload

# The server might be slow to stop, and that's fine. Don't kill it


So the first thing you need to do is enable the service as follows

/etc/systemd/system # systemctl enable postgresql.service

Created symlink from /etc/systemd/system/ to /usr/lib/systemd/system/postgresql.service.
/etc/systemd/system #

Then you need to validate the service starts and stops using :

service postgresql start

service postgresql status

service postgresql stop

After these few steps it now starts like a champ.

Good luck.

ABLE 529s vs 3rd Party Supplemental Needs Trusts

One of the most exciting things the Obama administration has accomplished with respect to caring for those with disabilities has been the creation of ABLE 529 accounts.   At first glance these accounts appear very much like a 3rd Party Supplemental Needs Trust.

The advantage to a ABLE 529 over a 3rd Party  Supplemental Needs Trust are cost, confidence, and ease of administration.    Setting up a 3rd Party Supplemental Needs Trust usually requires attorney time for drafting of the trust.   Then you have the open question of “will this trust be approved” when it is submitted for SSI and Medicaid etc., often there is a back and forth period between the attorney and the government, sometimes with the need to revise the trust etc.  Finally you need a trustee, corporate trustees are expensive, private family may lack the investing acumen and also fail to keep proper records etc. and jeopardize the trust by causing it to be declared a countable resource.   The ABLE 529 at first glance is a Supplemental Needs Trust in a can.   Fill out a few forms, submit some proof of disability etc. and voila you have a Supplemental Needs Trust with no need to have it drafted and absolute certainty it will be approved.

The single biggest difference with a 3rd Party Supplemental Needs Trust and ABLE 529 is that with the ABLE 529 the money is owned by the beneficiary, it is available to the beneficiary ( while not being a countable resource for benefits).   The beneficiary has the ability to withdraw the funds as they see fit, spend it as they see fit, and presumably keep the necessary records to document that their expenditures were appropriate.   On the one hand this gives the beneficiary a level of autonomy that in fact may be the goal in planning for a disabled person in the hope of their having a life that is as normal as possible.  On the other hand, depending on the nature of the disability, the nature of the person in general, etc. this may not be desirable.   A Trustee managing the trust may be better suited to ensuring the funds are not spent to soon and depleted before the life span of the beneficiary was completed.    The Trustee may be better at keeping the paperwork up to date to ensure that all expenditures meet the requirements of the various government agencies.   This can be particularly important for those with mental or behavioral disabilities, but can also be important for many people with only physical disability who, like many non disabled, simply cannot manage money for the long term and instead have spendthrift tendencies.

There are many people who are not under a guardianship but who need a gatekeeper to manage their money.   For example Social Security “Representative Payee” to manage their benefits.  The Supplemental Needs Trust manages this indirectly because of the requirement they never giving the beneficiary money directly.  The combination of Representative Payee and Supplemental Needs trust works in cases of fiscal irresponsibility mainly because the beneficiary never gets their hands on the money.   It is unclear how this would be achievable with a ABLE 529 at this time.  Its a question I’d like to see clarified.

A huge open question with ABLE 529s regards housing and how they will interact with HUD Section 8 subsidies.  Special Needs Trusts are having serious challenges threading the needle between the differing program requirement of HUD and SSI/Medicaid.   Of note ABLE 529 expenditures for housing will not reduce SSI/Medicaid, at least the way I read it but this should be verified.   SNT expenditures would reduce SSI under the In Kind Support and Maintenance ( ISM ) rules.   It may be that HUD will treat ABLE more favorably because it is a government program, but that remains to be seen.   Currently for non HUD Section 8 recipients I think housing money should be routed into the ABLE account and then back out to pay for rent without reducing SSI.

The other issue that limits the ABLE account is the $14,000 annual contribution limit, and to a lesser extent the limit on total assets in the account.  A beneficiary who will be left a significant amount of money will require a means to manage periodic distributions into the ABLE account.  The natural mechanism for that is a separate trust that exists along with the ABLE account.   Thus the ABLE account will not replace a 3rd Party Supplemental Needs Trust in the near term but instead will be a valuable tool complementing a Supplemental Needs Trust.  The able account can help to provide a channel for routing funds to work around ISM issues.   And issues around finding a competent trustee and reducing costs of establishing a 3rd Party Supplemental Needs Trust may be handled by using a competent Pooled Trust solution.

Fed or White House

The surprise election of Donald Trump to the White House has produced another surprise.  Sharply rising interest rates.  What was for 10 year treasuries an interest rate of 1.783 on Monday is on Friday 2.113 % as I type this article.

The FED has made no announcements, this interest rate swing is purely based on the speculative anticipation of a Trump presidency and the policies he will introduce.   This is significant as for the last several years the specter has existed that the FED primarily controlled the economy and did so purely with the interest rate lever and the QE purchase lever.

Whether Trumps policies will be good for the nation or not clearly remains to be seen.   Rising interest rates bode well for those looking to get income out of a portfolio, assuming its not purely rising rates from rising inflation.   Borrowers clearly will have a harder time of it.   All in though we need to get rates up off the floor, and in many cases up from under the floor, and back to a normal range.

Thoughts On November


Bank of Japan In Wonderland

According to financial news today the Bank of Japan has announced a couple key things.  First it wants to set a 10 yr bond target of zero percent.  Second it wants to hit its inflation target of 2% or perhaps slightly higher.   This is surrealism brought to finance.  Its not limited to Japan.

The ten year bond return of 0% would still be higher than the current bond yield in Japan which is slightly less than zero.   So zero is in fact an improvement.   Setting a 10 year bond target and controlling it is a stunning objective in and of itself.   The Bank of Japan, along with a lot of other central banks, have failed to achieve other targets.   All would prefer a return to normal economic growth and yields.  In the absence of the ability to control that they are now setting insanely absurd targets as a goal, like zero percent on a ten year bond.

Meanwhile markets have reacted to this news with gains for the day.   When the markets view this news as positive it gives us some sense of just how distorted things have become.

The thing to consider is why.   Stimulating growth is the goal of virtually all industrialized nations central banks.   The operating premise is to much cash is on hand, negative rates might force the cash to be invested.  Investing is how you produce growth because companies produce growth.   But its whistling past the graveyard.  The problem is not a lack of money to be invested, nor a lack of desire to invest it.   The problem is a lack of reasons to invest.  For growth to happen their must be an increase in demand on the part of the consumers.   This has not happened.   Japan is leading the way in that.  The reason is there are fewer and fewer consumers.  The way to increase demand is to increase the number of consumers.   Negative interest rates do not increase the number of consumers.


Life Expectancy and Retirement Planning

Managing retirement funds for yourself or for another is a challenge.   Part of challenge of answering “do I have enough money” or “how much money can I withdrawal each year” lies in knowing how long the person will live.   Its a very impolite and seemingly immoral question to ponder “When are you ( or I ) going to die ?”   In truth no one could answer it anyway.   But it lies at the very heart of planning.

One approach is to simply look at the average life expectancy by birth year and gender.  This information is available via the Social Security Administration, CDC and various online sources.   The IRS relies on expected life expectancy to calculate your required minimum distributions.  These of course are just averages.  People live longer, lots longer.  I believe the current average age at death is 83 years presently.   I know, and have known, several to live into their 90s.   I’ve known some to die much earlier.

The moral questions for funds you may manage for another center on, “Have I maximized the amount of money available to them during their life ?”.   If a longer than actual life expectancy is chosen as a budget basis the result will be excess money left over unused.  If a shorter than actual life expectancy is chosen the result will be running out of money in retirement and the pain of a drastic life style adjustment that goes with that.

Generally I believe that estimating high on life expectancy is the right path.   Its what I plan for myself and it is what I would suggest anyone plan on.  It also is the more palatable option than saying “Ya I think you ( or I ) are going to die much younger than we hope.”



Zero and Negative Yield Opportunity

As negative interest rates spread, Germany is also now offering negative bonds, an opportunity is created.   The longer rates stay near, at or below, zero the more a sovereign nation can finance its debt at zero interest.    Unlike consumers the sovereigns can than, to the extent debt is financed in their own currency, kick the inflation option full speed ahead after they get enough debt at 0% and make a lot of their current debt vanish through inflation.  To the extent doing that is in the interest of more than one nation, and it would be, that suggest a real possibility of a soon and sudden pivot back to inflation.