Setting up SQL Server vNext CTP 2.0 on Docker

Recently I moved into the brave new world of having a Macbook pro as my main laptop, and one of my first frustrations was the inability to be able to run SQL Server Management Studio locally. Unfortunately Microsoft haven’t come up with a solution for that one, so I’m having to do with a mix of SQLPro Studio and running SSMS inside a VDI machine on the mac.

One thing the Mac has made easier for me is the ability to start testing SQL Server vNext locally and without too much messing around. So when Microsoft released vNext 2.0 on the 19th April 2017, it seemed like a good opportunity to give it a try. (Whats new in SQL Server Linux)

This post should cover what you need to do to get a docker image of SQL Server running on macOS Sierra. The Microsoft documentation is pretty good on this subject, and not convoluted but I always find it good to blog how exactly I’ve done things, as there are always little quirks.

Install Docker for Mac

I downloaded the stable version just to be a bit safer and so that I wouldn’t introduce any unnecessary pinch points.

https://docs.docker.com/docker-for-mac/install/

Once installed, Start the Docker application – it may take a few minutes to initialise, but eventually you will end up with the docker ship icon in the top bar.

There are a few minimum requirements for running docker for the SQL Server image:

  • Docker Engine 1.8+ on any supported Linux distribution or Docker for Mac/Windows.
  • Minimum of 4 GB of disk space
  • Minimum of 4 GB of RAM

If your downloading a recent release of docker then the engine shouldn’t be an issue.

At the time of writing, my version is:

$ docker version
Client:Version	17.03.1-ce
API Version: 1.27
Go Version: go1.7.5

Server:
Version: 17.03.1-ce
API Version: 1.27 (minimum version 1.12)
OS/Arch: linux/amd64

Docker default starts with 2GB of RAM, but this can be easily changed to 4GB from the docker preferences in the advanced section.

Pull down and run the latest SQL Server Docker Image – Step by Step details here – https://docs.microsoft.com/en-gb/sql/linux/sql-server-linux-setup-docker

Quick Steps here:

sudo docker pull microsoft/mssql-server-linux

Note that I have added a –name switch to the docker command below. This is to simplify things once we have everything up and running as the container name is quite critical for more or less every command in docker.

sudo docker run --name SQL2017 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=' -p 1433:1433 -d microsoft/mssql-server-linux

To make sure we have successfully created our container, we can run:

docker ps -a

To familiarise yourself with other docker commands, you can run:

docker --help

Connect to the SQL Server

sudo docker exec -it SQL2017 /bin/bash

The flags are for the following:

-i, –interactive Keep STDIN open even if not attached–privileged Give extended privileges to the command-t, –tty Allocate a pseudo-TTY

This then takes you into the interactive command line for the docker image.

The folder structure by default is /var/opt/mssql for the database files & error logs. Sqlcmd is in /opt/mssql-tools/bin/

Reading the error log

While in exec mode for the SQL2017 container, run the following cat command:

cat /var/opt/mssql/errorlog

There is a very good guide by microsoft on how to troubleshoot SQL Server Linux which can be found here – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide

Using sqlcmd

Starting with SQL Server vNext CTP 2.0, the SQL Server command-line tools are included in the Docker image. If you attach to the image with an interactive command-prompt (as I have done above), you can run the tools locally.

First step is to add the sqlcmd path to the $PATH environment variable. This step isn’t critical but makes sqlcmd accessible from any location on the docker image

PATH=$PATH:/opt/mssql-tools/binecho 'export PATH="$PATH:/opt/mssql-tools/bin"'
~/.bash_profile 
root@5214e1df3c86:/opt/mssql-tools/bin# echo $PATH
/opt/mssql-tools/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
sqlcmd -S . -U sa -P
1> select name from sys.databases
2> go
name
-----------------------------------------------------------
master
tempdb
model
msdb

Installing SQLCMD on macOS

/usr/bin/ruby -e "$(curl -fsSL <a href="https://raw.githubusercontent.com/Homebrew/install/master/install" data-mce-href="https://raw.githubusercontent.com/Homebrew/install/master/install">https://raw.githubusercontent.com/Homebrew/install/master/install</a>)"
brew tap microsoft/mssql-preview <a href="https://github.com/Microsoft/homebrew-mssql-preview" data-mce-href="https://github.com/Microsoft/homebrew-mssql-preview">https://github.com/Microsoft/homebrew-mssql-preview</a> 
brew update 
brew install mssql-tools #for silent install ACCEPT_EULA=y 
brew install mssql-tools

 

Can be quickly tested locally by opening a terminal window and running:

sqlcmd -S my-remote-server -U test -P test
1&gt; select @@SERVERNAME
2&gt; go
-----------------------------------------------------------------
SQLSERVER/MYNAMEDINSTANCE
(1 rows affected)

Connecting into the docker image from host machine

Get the IP of the container

MCR-AL33450:/mohsin.alipatel$ docker inspect --format "{{ .NetworkSettings.Ports}}" SQL2017
map[1433/tcp:[{0.0.0.0 1433}]]

Then make a sqlcmd connection using the IP and port above

MCR-AL33450:/mohsin.alipatel$ sqlcmd -S 0.0.0.0,1433 -U sa -P
1&gt;Select @@servername
2&gt;go-----------------------------------------------------------------------------------------------
5212e1df3c86
(1 rows affected)

This should provide a basic introduction to both docker and to SQL server on linux.

Retrieve Oracle SPID from unix OS PID

The Unix/Linux PID that you see in ‘top’ is visible as SPID in V$PROCESS. Then, you’d have to join V$PROCESS to V$SESSION to get the SID. (Note : PID in V$PROCESS is the Oracle PID, not the OS PID).

--Replace bind variable with OS PID List
select s.sid, s.serial#, s.username,
       to_char(s.logon_time,'DD-MON HH24:MI:SS') logon_time,
       p.pid oraclepid, p.spid "ServerPID", s.process "ClientPID",
       s.program clientprogram, s.module, s.machine, s.osuser,
       s.status, s.last_call_et
from  gv$session s, gv$process p
where p.spid=nvl('&unix_process',' ')
and s.paddr=p.addr
order by s.sidÔ…

Apache Rewrite Rules

I recently worked on Migrating an Application from one oracle database to another whch included transferring over to the new htmldb apex.

I wanted to append a redirect rule onto a web link to add HTMLDB path to send users to the correct application.

Amending Apache Conf:

cd /u01/app/oracle/product/10.1/htmldb/Apache/Apache/conf/includes
more myappconfig

[LIVE]:/u01/app/oracle/product/10.1/htmldb/Apache/Apache/conf/includes : more myappconfig

<VirtualHost *>
        ServerName myapp.gen.mycompany.net
        ServerAlias myapp.live.mycompany.net
        ServerAlias myapp.live.dc1.mycompany.net
        ServerAlias myapp.live.dc2.mycompany.net
        ServerAdmin
webmaster@mycompany.co.uk
        DocumentRoot "/u01/app/oracle/product/10.1/htmldb/Apache/Apache/htdocs"

        RewriteEngine    on
        RewriteLogLevel  0

        RewriteRule ^/pls/htmldb/f   /pls/htmldb2/f?p=152:1 [R=301]
        RewriteRule ^/$ /pls/htmldb2/f?p=152:1 [R=301]
</VirtualHost>

A rewrite rule of

RewriteRule ^/$ /pls/htmldb2/f?p=152:1 [R=301]

Will append the ‘/pls/htmldb2/f?p=152:1’  anyone who goes to any of the server alias

A rewrite rule of

RewriteRule ^/pls/htmldb/f   /pls/htmldb2/f?p=152:1 [R=301]

Will change any addresses which come in with ‘^/pls/htmldb/f’ to change to ‘/pls/htmldb2/f?p=152:1’

To Add logging to the Rewrite config add the following:

       #RewriteLog  /u01/app/oracle/product/10.1/htmldb/Apache/Apache/logs/myapp.rewrite.log
       #ErrorLog    /u01/app/oracle/product/10.1/htmldb/Apache/Apache/logs/myapp.error.log
       #CustomLog   /u01/app/oracle/product/10.1/htmldb/Apache/Apache/logs/myapp.access.log combined

–to see database connectivity for apache and htmldb

/u01/app/oracle/product/10.1/htmldb/Apache/modplsql/conf : more marvel.conf

Backgrounding a linux task

Problem:  Want to make a Oracle mview refresh run in the background so it doesnt timeout after 2 hours of running (our company’s network timeout)

On the Oracle Server

  • Create the file with the SQL code you want to run. Mine is called mv_fp.sql
  • BEGIN
      DBMS_SNAPSHOT.REFRESH(
        LIST => 'SCHEMA.MV_MVIEW'
       ,METHOD => 'C'
       ,PUSH_DEFERRED_RPC => TRUE
       ,REFRESH_AFTER_ERRORS => FALSE
       ,PURGE_OPTION => 1
       ,PARALLELISM => 4
       ,ATOMIC_REFRESH => TRUE
       ,NESTED => FALSE);
    END;
    /
  • Create the file with the shell code you want to run. Mine is called mv_fp.sh
  • export ORACLE_SID=MYDB
    sqlplus schema/password << eof
    @mv_fp.sql
    exit
    eof
    • Grant execute access on the .sh file

    chmod 744 mv_fp.sh
    • Run the following to background the task

    nohup ./mv_fp.sh > nohup_mv_fp.out 2>&1 &

Useful Unix Commands

Remove archive log files older than 2 days:

-- list them first to confirm
find .*.arc -mtime +2 -type f -maxdepth 1 -exec ls -ltr {} \;
-- then delete    
find .*.arc -mtime +2 -type f -maxdepth 1 -exec rm {} \;

Remove trace files – same as above:

-- list them first to confirm
find . -name "*.trc" -mtime +90 -type f                        
-- then delete
find . -name "*.trc" -mtime +90 -type f -exec rm -f '{}' \; 

Different way of doing the above:

-- to find files. Value is number of days
find . -maxdepth 1 -name "*.xml"  -mtime +10 -type f

-- to delete files. value is number of days
find . -maxdepth 1 -name "*.xml" -mtime +10 -type f -exec rm -f '{}' \;

Loop a command:

while true; do crs_stat -t; sleep 5; done

Copy Files from one server to another:

scp <file> <user>@<target>:<path>
eg: scp test.txt oracle@SERVERA:/export/home/oracle

Messages on linux:

var/log/messages

Changing Permissions and Owners:

-- Change owner
chown <owner> <file>
chown oracle CSSfile
-- Change group
chgrp <group> <file>
chgrp dba CSSfile
-- Change permissions
r = 4    w = 2   x = 1
owner|group|all
  421   |  421   |421
  rwx       rwx     rwx
--for read, write, all for all would be:
chmod 777
--for drwxr-xr-x
chmod 755

Tar files:

-- taring a folder
tar -cvfp <tar filename/location> <top level of dir you want to tar>
-- Notes
-- c = create file v = verbose f = files p = preserve permissions
-- Untaring a folder
tar -xvf <name of tar>
-- untaring a folder to location
tar -xvf <name of tar> -C <dir to untar to>

Search and replace:

:%s/<SEARCH_STRING>/<REPLACE_STRING>/g    - /g means global

Crontab

Commands

crontab -e     Edit your crontab file, or create one if it doesn’t already exist.
crontab -l      Display your crontab file.
crontab -r      Remove your crontab file.
crontab -v      Display the last time you edited your crontab file. (This option is only available on a few systems.)

Example

#############################################################
#
# Oracle broken jobs
#

0 9 * * *  /path/failingjobs.sh > /path/failingjobs.out  2>&1

Date time config for scheduling jobs

.---------------- minute (0 - 59) 
|  .------------- hour (0 - 23)
|  |  .---------- day of month (1 - 31)
|  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ... 
|  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7)  OR sun,mon,tue,wed,thu,fri,sat 
|  |  |  |  |
*  *  *  *  *  command to be executed

There are several ways of specifying multiple date/time values in a field:

  • The comma (‘,’) operator specifies a list of values, for example: "1,3,4,7,8" (space inside the list must not be used)
  • The dash (‘-‘) operator specifies a range of values, for example: "1-6", which is equivalent to "1,2,3,4,5,6"
  • The asterisk (‘*’) operator specifies all possible values for a field. For example, an asterisk in the hour time field would be equivalent to ‘every hour’ (subject to matching other specified fields).

There is also an operator which some extended versions of cron support, the slash (‘/’) operator (called "step"), which can be used to skip a given number of values. For example, "*/3" in the hour time field is equivalent to "0,3,6,9,12,15,18,21".

So "*" specifies ‘every hour’ but the "*/3" means only those hours divisible by 3. The meaning of ‘/’ specifier, however, means "when the modulo is zero" rather than "every". For example, "*/61" in the minute will in fact be executed hourly, not every 61 minutes.

Example: the following will clear the Apache error log at one minute past midnight ( 00:01 of every day of the month, of every day of the week ).

1 0 * * *  echo -n "" > /www/apache/logs/error_log

Slash example: the following will run the script /home/user/test.pl every 5 minutes.

*/5 * * * *  /home/user/test.pl