Bitz-N-Bitez

Category: Technology

Postgres : Convert entire row to text with type conversion

One of the neat ways you can use postgres type conversion is to display an entire row in a format suitable for using in a subsequent insert etc.   That is comma delimited, quoted as needed between two parenthesis.

First lets document the customer table in this sample dvdrental database.

CREATE TABLE customer (

customer_id integer DEFAULT nextval('customer_customer_id_seq'::regclass) NOT NULL,
store_id smallint NOT NULL,
first_name character varying(45) NOT NULL,
last_name character varying(45) NOT NULL,
email character varying(50),
address_id smallint NOT NULL,
activebool boolean DEFAULT true NOT NULL,
create_date date DEFAULT ('now'::text)::date NOT NULL,
last_update timestamp without time zone DEFAULT now(),
active integer

);

Now lets produce the rows converting the whole row to text as follows….

dvdrental=# select t::text from customer t limit 5;
t
-------------------------------------------------------------------------------------------------------
(524,1,Jared,Ely,jared.ely@sakilacustomer.org,530,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(1,1,Mary,Smith,mary.smith@sakilacustomer.org,5,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(2,1,Patricia,Johnson,patricia.johnson@sakilacustomer.org,6,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(3,1,Linda,Williams,linda.williams@sakilacustomer.org,7,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(4,2,Barbara,Jones,barbara.jones@sakilacustomer.org,8,t,2006-02-14,"2013-05-26 14:49:45.738",1)
(5 rows)

dvdrental=#

While I don’t have an immediate need for this conversion, its still a fascinating little postgres trick.

Advertisements

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

GO

 

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 https://packages.microsoft.com/keys/microsoft.as 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
OK
root@ip-172-30-0-180:/etc# curl https://packages.microsoft.com/config/ubuntu/16. 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] https://packages.microsoft.com/ubuntu/16.04/mssql-server 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 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial InRelease
Get:2 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates InRelease [1 02 kB]
Get:3 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports InRelease [102 kB]
Get:4 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main Sources [868 kB ]
Get:5 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/restricted Sources [ 4,808 B]
Get:6 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/universe Sources [7, 728 kB]
Get:7 http://security.ubuntu.com/ubuntu xenial-security InRelease [102 kB]
Get:8 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/multiverse Sources [ 179 kB]
Get:9 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main Sources [206 kB]
Get:10 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/restricted Sources [1,804 B]
Get:11 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/universe So urces [110 kB]
Get:12 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/multiverse Sources [3,648 B]
Get:13 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 Packages [426 kB]
Get:14 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main Transl ation-en [164 kB]
Get:15 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/restricted amd64 Packages [6,576 B]
Get:16 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/restricted Translation-en [2,016 B]
Get:17 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/universe am d64 Packages [365 kB]
Get:18 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial InRelease [2,828 B]
Get:19 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/universe Tr anslation-en [132 kB]
Get:20 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/multiverse amd64 Packages [7,384 B]
Get:21 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/multiverse Translation-en [2,988 B]
Get:22 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/main Sour ces [3,180 B]
Get:23 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/universe Sources [1,868 B]
Get:24 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/main amd6 4 Packages [4,392 B]
Get:25 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/main Tran slation-en [3,104 B]
Get:26 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/universe amd64 Packages [2,412 B]
Get:27 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports/universe Translation-en [1,216 B]
Get:28 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial/main amd6 4 Packages [940 B]
Get:29 http://security.ubuntu.com/ubuntu xenial-security/main Sources [49.1 kB]
Get:30 http://security.ubuntu.com/ubuntu xenial-security/restricted Sources [1,8 04 B]
Get:31 http://security.ubuntu.com/ubuntu xenial-security/universe Sources [14.4 kB]
Get:32 http://security.ubuntu.com/ubuntu xenial-security/multiverse Sources [728 B]
Get:33 http://security.ubuntu.com/ubuntu xenial-security/main amd64 Packages [17 7 kB]
Get:34 http://security.ubuntu.com/ubuntu xenial-security/main Translation-en [71 .2 kB]
Get:35 http://security.ubuntu.com/ubuntu xenial-security/restricted amd64 Packag es [6,576 B]
Get:36 http://security.ubuntu.com/ubuntu xenial-security/restricted Translation- en [2,016 B]
Get:37 http://security.ubuntu.com/ubuntu xenial-security/universe amd64 Packages [62.6 kB]
Get:38 http://security.ubuntu.com/ubuntu xenial-security/universe Translation-en [33.9 kB]
Get:39 http://security.ubuntu.com/ubuntu xenial-security/multiverse amd64 Packag es [2,764 B]
Get:40 http://security.ubuntu.com/ubuntu 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
binfmt-support
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
python2.7-minimal
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 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 l ibpython2.7-minimal amd64 2.7.12-1ubuntu0~16.04.1 [339 kB]
Get:2 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 p ython2.7-minimal amd64 2.7.12-1ubuntu0~16.04.1 [1,295 kB]
Get:3 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 python-mi nimal amd64 2.7.11-1 [28.2 kB]
Get:4 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 l ibc6 amd64 2.23-0ubuntu4 [2,586 kB]
Get:5 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 l ibpython2.7-stdlib amd64 2.7.12-1ubuntu0~16.04.1 [1,884 kB]
Get:6 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 p ython2.7 amd64 2.7.12-1ubuntu0~16.04.1 [224 kB]
Get:7 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 libpython -stdlib amd64 2.7.11-1 [7,656 B]
Get:8 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 python am d64 2.7.11-1 [137 kB]
Get:9 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 g cc-5-base amd64 5.4.0-6ubuntu1~16.04.4 [16.8 kB]
Get:10 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libstdc++6 amd64 5.4.0-6ubuntu1~16.04.4 [393 kB]
Get:11 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 libbabel trace1 amd64 1.3.2-1 [34.7 kB]
Get:12 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 libbabel trace-ctf1 amd64 1.3.2-1 [88.3 kB]
Get:13 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 gdb amd64 7.11.1-0ubuntu1~16.04 [2,525 kB]
Get:14 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 gdbserver amd64 7.11.1-0ubuntu1~16.04 [172 kB]
Get:15 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libcc1-0 amd64 5.4.0-6ubuntu1~16.04.4 [38.8 kB]
Get:16 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libcurl3 amd64 7.47.0-1ubuntu2.2 [186 kB]
Get:17 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/main amd64 libunwin d8 amd64 1.1-4.1 [46.5 kB]
Get:18 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/universe amd64 libc ++1 amd64 3.7.0-1 [226 kB]
Get:19 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libc6-dbg amd64 2.23-0ubuntu4 [3,679 kB]
Get:20 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial/universe amd64 libj emalloc1 amd64 3.6.0-9ubuntu1 [78.9 kB]
Get:21 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial/main amd6 4 mssql-server amd64 14.0.1.246-6 [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 (14.0.1.246-6) ...
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 (14.0.1.246-6) ...

+-------------------------------------------------------------------+
| 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) ...
root@ip-172-30-0-180:/etc#

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

The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746388 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/multi-user.target.wants/mssql-server.service to /lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service 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]
root@ip-172-30-0-180:/tmp#

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
UID PID PPID C STIME TTY TIME CMD
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
root@ip-172-30-0-180:/var/opt/mssql/data#

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.


CREATE DATABASE [TESTDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TESTDB', FILENAME = N'C:\var\opt\mssql\data\TESTDB.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'TESTDB_log', FILENAME = N'C:\var\opt\mssql\data\TESTDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

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

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

 

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


[Unit]

Description=PostgreSQL database server
After=syslog.target
After=network.target

[Service]
Type=forking
User=postgres
EnvironmentFile=-/etc/sysconfig/postgresql
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
SendSIGKILL=no

[Install]
WantedBy=multi-user.target

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/multi-user.target.wants/postgresql.service 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.

EPSON XP-820 Sheet Feed is AWESOME

I am finishing up the last few sheets of paper that I am scanning.   I decided to digitize the approximately 1 foot tall stack of notes and printouts with notes I have accumulated over the years.  I do not actively work with them anymore.

These documents were anything but pristine paper.  They had been stapled, unstapled, re-stapled.  They had dog ears.  They had creases that had started to separate.   They had torn leading edges.

The EPSON XP-820 just processed them flawlessly.  In one case I did have a single sheet jam and I butchered it extracting it.  So I just photocopied it on the printer and scanned the copy.  In all other cases it behaved beyond belief.  I fully expected nothing but problems scanning this stuff.  I am simply amazed at how good a job the engineers did with the paper handling system on this scanner/printer.

Surprisingly The Best Android Tablet Stand

PictureStand

I have discovered, quite by accident, that the best stand for a 10″ Samsung tablet is a cheap folding picture stand like the one pictured above.   It holds the tablet far enough above the surface of the desk so that you have enough clearance to attach a USB charging cable.   It holds the tablet at an angle suitable for viewing.  It can accommodate landscape and portrait.  Most importantly I can leave the tablet in my usb keyboard case, simply folding that out of the way.

I’ve never seen this marketed for a tablet stand, but I can say it works remarkably well.  Better than any other solution, and I’ve tried several.

Toad Data Point, Postgres ODBC, and Windows 7

I recently rpostgresql_elephantan into complexity while installing Postgres 9.3.03 ODBC drivers and data source setup for Toad Data Point 3.2 on Windows 7 Enterprise 64 bit.   I’ll spare you the details of how I got to the following conclusions, but here they are :

  1. You want to install the 32 bit Postgres ODBC Drivers not the 64 bit ones.  Actually you can install both 32 and 64 but you have to configure the 32 bit data source.   In Toad when you click thru to the ODBC configuration tool you wind up in the 32 bit version.
  2. The 32 bit ODBC admin tool is located here : c:\Windows\SysWOW64\odbcad32.exe
  3. The 64 bit ODBC admin tool is located here : c:\Windows\System32\odbcad32.exe
  4. Yes it looks like I got the 32 bit paths backwards in #2 and #3, I didn’t thats really how it is.   Also both the 64 and 32 bit versions are in fact named odbcad32.exe.  Chalk it up to some bad mushrooms at Redmond.
  5. This is critical. If you already have the 64 bit odbc admin tool open when you try to launch the 32 bit odbc admin tool, the 32 bit version doesn’t actually start.  Instead the 64 bit admin tool simply moves to the foreground on your screen.  The opposite is also true if you are running the 32 bit version and try to launch the 64 bit version.

If you d/l and install the 32 bit odbc drivers for postgres and configure the data source using the 32 bit admin tool then you can easily connect via Toad Data Point.  If you do something else…. well … consider it a learning opportunity.

Cheers!

Automated Benchmarking

Naturally the best benchmark is your own workload.  Any canned benchmark will be different than the live workload you witness on your particular system.   Still for kicking the tires on a new DBMS installation, for exploring tuning impacts of various variables in general etc. canned benchmarks can be marvelous.

HammerDB appears to be just the ticket.   It supports TPC-C and TPC-H against SQLServer, MySql, Oracle, Greenplum, Postgres and Redis.   It runs on windows and linux.

http://www.hammerdb.com/about.html

Check it out at the above link.

Sandbox in EC2 … cost safety ….

I’ve found EC2 in Amazon AWS to be a great sandbox for a DBA.   I have test servers of SQL Server 2012 and 2014 base at the moment.   I always worry though that I will forget to shutdown an instance when I am not using it, go on vacation to Pango Pango, or wherever, and return to discover I’ve been paying hourly fees for an instance I didn’t need to have running.

What to do ?

The answer is to schedule a task to automatically shutdown the server once a day, a a time when you probably won’t be using it anyway.   Say 3:00 am local time.   If by chance you wanted to have stuff running overnight in your EC2 test instance…. just disable the auto shutdown.

You will want to start the task scheduler, create a basic tast that runs daily, specify the time, and then enter the command and options.  The command is :

C:\Windows\System32\shutdown.exe

Arguments that I use are ‘-s -t 60 -f -c “Bye Be” ‘

To learn more about shutdown.exe read the following technote : http://technet.microsoft.com/en-us/library/bb491003.aspx

What Version of SQL Server Is Running ?

When you sit down for the first time with a new SQLServer instance its a good idea to validate precisely what version you have running.  I have been told I have one version when in fact I have another.   In large installations of servers its even possible you may have forgotten what a particular server is running.

First you can view the SQL Log “Management->Sqlserver Logs->…. the first record will usually show the server name.  You may have to widen the column to see it all.

Another way is to use the @@VERSION variable.   Instead of using SELECT @@VERSION which returns the results in a single text column I prefer to use PRINT @@VERSION which formats the output in a more human readable display.

Microsoft SQL Server 2014 – 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

In the above example it shows I am using the 64 bit version of SQLSERVER 2014 Standard Edition