SQL Server Application Intent Read Only with TinyTDS

README.md

Read-Only Intent with TinyTDS

Prerequisite

• Docker

Also set following environment variables: DB_HOST, DB_NAME, DB_USERNAME, DB_PASSWORD

Install dependencies

$ docker build -t tiny-tds-image .

Run with application intent

Check if the right server is executing the query by printing the server name.

$ ./run.sh get-server-name.rb

Dockerfile

FROM ruby:2.5

RUN apt-get update -y \
    && apt-get install -y wget build-essential libtool-bin libc6-dev libssl1.0-dev \
    && mkdir -p /opt \
    && cd /opt \
    && wget http://www.freetds.org/files/stable/freetds-1.1.6.tar.gz \
    && tar -xzf freetds-1.1.6.tar.gz \
    && cd freetds-1.1.6 \
    && ./configure --prefix=/usr/local --with-tdsver=7.3 \
    && make \
    && make install

WORKDIR /app

COPY . .

RUN bundle install

CMD ["bundle", "exec", "ruby", "-v"]

Gemfile

source 'https://rubygems.org'

gem 'tiny_tds', git: 'git://github.com/aharpervc/tiny_tds.git', ref: '819810b7cde3c723f6ae9b00c35ac6e422d71ab8'

Gemfile.lock

GIT
  remote: git://github.com/aharpervc/tiny_tds.git
  revision: 819810b7cde3c723f6ae9b00c35ac6e422d71ab8
  ref: 819810b7cde3c723f6ae9b00c35ac6e422d71ab8
  specs:
    tiny_tds (2.1.1)

GEM
  remote: https://rubygems.org/
  specs:

PLATFORMS
  ruby

DEPENDENCIES
  tiny_tds!

BUNDLED WITH
   1.17.3

get-server-name.rb

require 'tiny_tds'

client = TinyTds::Client.new(
  host: ENV['DB_HOST'],
  username: ENV['DB_USERNAME'],
  password: ENV['DB_PASSWORD'],
  database: ENV['DB_NAME'],
  read_only_intent: true,
  contained: true,
  message_handler: Proc.new { |m| puts m.message }
)

client.execute('print @@servername').do

run.sh

#!/bin/bash

set -euo pipefail

work_dir=/app
image_name=tiny-tds-image
script_name=$1

docker run -it --rm --name tiny-tds \
    -e DB_HOST -e DB_USERNAME -e DB_PASSWORD -e DB_NAME \
    -v `pwd`:$work_dir \
    -w $work_dir \
    $image_name \
    bundle exec ruby $script_name