Publishing SQL Server database in Docker

To me docker containers have an ethereal, almost unreal quality to them. Do they really exist? Where are they? What do they look like? To convince myself of their reality I want to use SQL Server Management Studio on the host to connect to a SQL instance in a running container. Along the way we shall

– bring up the container
– access container command shell
– find out where it is on the network
– connect to it from the host machine
– compare static and dynamically assigned container IPs

 Environment

I’m on windows 10 and I’m going to be working with windows containers.

docker-compose.yml

For this I don’t need a DockerFile as I’m just going to run a library image directly so I’m going straight to a docker compose file docker-compose.yml

version: '3.2'

services:
  db:
    image: microsoft/mssql-server-windows-developer
    ports:
      - "49401:1433"
    environment:
      - sa_password=Secret12345
      - ACCEPT_EULA=Y      
    container_name: sqlserver_db1

networks:
  default:
    external:
      name: nat


so to break it down

image: microsoft/mssql-server-windows-developer

Create the container from the image microsoft/mssql-server-windows-developer. If it hasn’t been downloaded then it will be when we bring up the container

ports:
- "49401:1433"

we are running on port 1433 internally to the container and the port 49401 will be bound to the host. I’m not running it on 1433:1433 because I’ve already got SQL Server on my host so that port is taken. Attempts to bind the host to a port that is already taken generates odd errors.

environment:
- sa_password=Secret12345
- ACCEPT_EULA=Y

we set the system admin password and accept the licence agreement

container_name: sqlserver_db1

and the container is given a name rather than one assigned by docker. It just keeps the rest of the examples easier. It’s not needed.

We haven’t specified a subnet or IP address so the network section tells docker to use its default network connection

Bringing the container up

To bring it up run

docker-compose up

which runs and brings up the docker container

If we jump onto another cmd window and run

docker container ls

We get the list of running containers thus

so we can see our sql box in it’s container in the platonic realm of container space that it is currently inhabiting. It’s called sqlserver_db1 as specified in our docker-compose.yml file

Getting an interactive shell on our container

The container still has an air on unreality to it. To start to resolve it into the real world I want to be able to run commands on it. To do this we need to bring up an interactive shell. I can bring up a cmd window

docker exec -ti sqlserver_db1 cmd

or a powershell window for more options

docker exec -ti sqlserver_db1 powershell

I’m doing the powershell. Once there I cna start to run whatever commands I see fit

echo 'Hello world'
hostname

and so on.

ping google.commands

is a good one to run to check if the container has network connectivity to the outside world. Mine didn’t and it took a while to work out why.

Where is it on the network

It’s not immediately apparent where the container is on the network. We’ve let docker dynamically assign the IP. To find it we go to our interactive shell and run

ipconfig

which will tell use the IP4 address

or outside of an interactive shell (from the host) we could use

docker inspect -f "{{ .NetworkSettings.Networks.nat.IPAddress }}" sqlserver_db1

which will tell us the IP4 address also.

So right now our container is on 192.168.154.121 and we can ping it there. Going back to docker-compose.yml we also specify ports.

ports:
- "49401:1433"

so 1433 is the one that the docker container uses and we can telnet to it from the host to prove it’s open

telnet 192.168.154.121 1433

which it is. Also we can telnet to the host on the other port

telnet 127.0.0.1 49401

which is the same place but via the port that is bound to the host.

Connecting to the container with SSMS

I like to see things to full accept their existence. To convince my inner self let’s connect to the container with SQL Server Management Studio

We can connect to the host on its IP4

Or on the host IP by specifying the loopback IP and the port as we have bound the host to a port other than the default for SQL Server (1433).

Note that to connect via SSMS to a specific port the IP is separated by a comma i.e.

127.0.0.1,49401

Now we can see our container database from the host. We are convinced. It exists.

Connecting with a static IP

We can also assign the database container a static IP address by specifying a subnet and an IP address for our container in the docker-compose.yml file thus

version: '3.2'

services:
  db:
    image: microsoft/mssql-server-windows-developer
    ports:
      - "49401:1433"
    environment:
      - sa_password=Secret12345
      - ACCEPT_EULA=Y      
    networks:
      vpcbr:
        ipv4_address: 10.5.0.5
    container_name: sqlserver_db1
    
networks:
  vpcbr:
    driver: nat
    ipam:
     config:
       - subnet: 10.5.0.0/16      

The container will now always be on 10.5.0.5 and we can connect with SSMS on 10.5.0.5 without worrying about any of the intervening steps. I guess that’s the easy way.

Demo Code

In case anyone needs it the docker files for static and dynamic IP implementations are at on my github site here.

Useful Links

https://docs.docker.com/compose/compose-file/
Full specification of docker compose

Selecting from a column with possible NULLs in T-SQL

I write blog posts for different reasons. Sometimes it’s because things aren’t well documented elsewhere, sometimes it’s new and interesting and sometimes it’s purely for my own amusement. Occasionally I write things as I kind of penance – things I should know about but bizarrely forgot or misunderstood. This is one of those. It’s how to select from a column with possible NULLs and not omit expected rows from the results set. This has been documented at length elsewhere but that’s not the point of penance.

Test Cases – equals operator

Working through some examples when comparing NULLs with the equals operator.

NULL is equal to ‘test’

SELECT CASE WHEN NULL = 'test' THEN 'true' ELSE 'false' END

False: as expected

NULL is not equal ‘test’

SELECT CASE WHEN NULL <> 'test' THEN 'true' ELSE 'false' END

False:  less expected

NULL is not equal to NULL

SELECT CASE WHEN NULL <> NULL THEN 'true' ELSE 'false' END

False: more expected

NULL is equal to NULL

SELECT CASE WHEN NULL = NULL THEN 'true' ELSE 'false' END

False: odd again

NULL isn’t equal to anything including itself when using the equals operator. I know this really and we see the same kind of thing in JavaScript.

Test cases – IS NULL

Of course it well known that equals doesn’t do the job with NULLS. The IS NULL operator is correct

Null is null

SELECT CASE WHEN NULL IS NULL THEN 'true' ELSE 'false' END

True.

Null is not null

SELECT CASE WHEN NULL IS NOT NULL THEN 'true' ELSE 'false' END

False.

All as expected.

Test case -selecting from a column with possible NULLs

So this is what tripped me up – when you have a column that is nullable and trying to filter as a string.

DECLARE @TestTable TABLE (Id INT NOT NULL, Comment VARCHAR(100))
INSERT INTO @TestTable (Id, Comment) VALUES (1, 'Test')
INSERT INTO @TestTable (Id, Comment) VALUES (2, 'Other Value')
INSERT INTO @TestTable (Id, Comment) VALUES (3,  NULL)

SELECT * FROM @TestTable WHERE Comment <> 'Test'

I naively thought that there are 2 rows that aren’t ‘Test’ so we will get those. But since NULL is not equal to ‘Test’ and not not equal to ‘Test’ then actually we get one less row than we expected

The correct select is

DECLARE @TestTable TABLE (Id INT NOT NULL, Comment VARCHAR(100))
INSERT INTO @TestTable (Id, Comment) VALUES (1, 'Test')
INSERT INTO @TestTable (Id, Comment) VALUES (2, 'Other Value')
INSERT INTO @TestTable (Id, Comment) VALUES (3,  NULL)

SELECT * FROM @TestTable WHERE COALESCE(Comment, '') <> 'Test'

Which converts the NULL to an empty string so then works

SELECT * FROM @TestTable WHERE ISNULL(Comment, '') <> 'Test'

Also works and is the same. Penitential blog post over.

Useful Links

https://stackoverflow.com/questions/18828641/sql-difference-between-coalesce-and-isnull
Comparison of ISNULL and COALESCE

http://adripofjavascript.com/blog/drips/equals-equals-null-in-javascript.html
It’s not just T-SQL. Special handling around NULLs in JavaScript

SQL Server restore fails if backups taken in rapid succession

How I (unfairly) think of SQL Server

I don’t spend an awful lot of time thinking about SQL Server. To me it’s a workhorse which I think is a compliment. * It’s steady, reliable and gets the job done. It’s not glamorous though and people don’t talk about it at dinner parties.

I did have cause to give the old SQL Server workhorse a bit more thought recently. I’m having to do a bit of database work and I’m taking lots of backups in rapid succession and restoring them. For some reason the backups didn’t restore the most recent version of the database. The restored DB was weirdly out of date. I got round this by going to options on the backup dialog and selecting ‘Overwrite existing backup sets’

Once I’d blatted out the existing sets then it worked fine. I don’t know why it wouldn’t work if I appended to backup sets and clearly this is a development environment workaround. I’m not advocating people merrily wiping out backup sets on production sets. A better workaround might have been to start using snapshots but once the workhorse was lashed back to the plough then I was happy. That said, I’d be very interested to know why it was doing that if anyone knows.

* On reflection, I wouldn’t like it if called me a workhorse so perhaps it’s not as much of a compliment as I imagine.

T-SQL Random Data Generator

I’ve often had the need to populate database tables with chunks of random data for testing and so forth and this random data generator stored procedure does the trick for me. I’ve had it knocking round in my sock draw for quite a while so I’ve dusted it off and updated it. It actually does a bit more than I remembered. I’ve used it on SQL Server 2005 and 2008 and the INFORMATION_SCHEMA use should make it forwardly compatible.

Code

usp_RandomInsertGenerator

This procedure actually does the heavy lifting and generates the insert statements.

Create Procedure usp_RandomInsertGenerator
(
 @TableName nvarchar(128)
)
As

-- does foreign keys
-- max character length
-- standard data types
-- identity columns
-- detects nullable columns and leaves as null

-- does not do
-- omitted datatypes - binary, varbinary, image, timestamp

-- Hacks
-- foreign key links where linked to child table via a unique index rather than a primary key. Assumpts FK of 1

-- assumes 
-- foreign keys are of a numeric type i.e. ints, tinyints, floats etc...
-- assumes foreign key constraints exist

Begin

Set NoCount On

Declare @ColumnName nvarchar(128)
 Declare @FK_TableName nvarchar(128)
 Declare @FK_ColumnName nvarchar(128)
 Declare @ConstraintName nvarchar(128)
 Declare @DataType nvarchar(128)
 Declare @CharacterMaximumLength int
 Declare @Sql nvarchar(max)
 Declare @MaxValue int
 Declare @InsertValue nvarchar(400)
 Declare @SqlOutputFields nvarchar(max)
 Declare @SqlOutputValues nvarchar(max)
 Declare @FirstLoop bit
 Declare @IsIdentity bit
 Declare @StringInsert bit
 Declare @DummyText varchar(48)

Set @FirstLoop = 1
 Set @SqlOutputFields = 'INSERT INTO ' + @TableName + ' ('
 Set @SqlOutputValues = ' VALUES ('
 Set @DummyText = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'

-- cursor loops through every column for named table
 Declare procCursor CURSOR FORWARD_ONLY
 For 
 Select 
 COLUMN_NAME, 
 DATA_TYPE, 
 CHARACTER_MAXIMUM_LENGTH 
 From 
 INFORMATION_SCHEMA.COLUMNS col
 Where 
 TABLE_NAME = @TableName
 And IS_NULLABLE = 'NO'
 Order By 
 ORDINAL_POSITION

Open procCursor
 Fetch Next From procCursor Into @ColumnName, @DataType, @CharacterMaximumLength

while @@fetch_status <> -1
 Begin
 
 -- datatypes i have not bothered implementing
 if @DataType = 'cursor' OR @DataType = 'timestamp' 
 Or @DataType = 'binary' Or @DataType = 'varbinary' Or @DataType = 'image'
 Begin
 Raiserror('Unsupported Data Type', 1, 16)
 End

--reset variables
 Set @FK_TableName = ''
 Set @FK_ColumnName = ''
 Set @StringInsert = 0
 Set @ConstraintName = ''
 
 -- Do not add in an insert value if the loop is an identity
 Select @IsIdentity = COLUMNPROPERTY(OBJECT_ID(@TableName),@ColumnName,'IsIdentity')
 if @IsIdentity = 1 
 Begin
 Fetch Next From procCursor Into @ColumnName, @DataType, @CharacterMaximumLength
 continue
 End
 
 -- getting the value to be inserted for this data type
 if @DataType = 'varchar' Or @DataType = 'nvarchar' 
 Or @DataType = 'char' Or @DataType = 'nchar'
 Or @DataType = 'text' Or @DataType = 'ntext'
 Begin
 Set @StringInsert = 1
 Set @InsertValue = SubString(@DummyText, 1, @CharacterMaximumLength)
 End
 Else if @DataType = 'datetime' Or @DataType = 'smalldatetime'
 Begin
 Set @StringInsert = 1
 Set @InsertValue = Cast(GetDate() as varchar(20))
 End
 Else if @DataType = 'uniqueidentifier'
 Begin
 
 Set @StringInsert = 1
 Set @InsertValue = Cast(NewId() as varchar(200))
 End
 Else -- it is some key of numeric type
 Begin
 
 -- getting the child table indexes
 Set @Sql = '
 Select 
 @FK_TableName = pkconst.TABLE_NAME,
 @FK_ColumnName = pkconst.COLUMN_NAME,
 @ConstraintName = coluse.CONSTRAINT_NAME
 From 
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE coluse 
 Join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fkconst 
 On fkconst.CONSTRAINT_NAME = coluse.CONSTRAINT_NAME
 Left Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkconst 
 On pkconst.CONSTRAINT_NAME = fkconst.UNIQUE_CONSTRAINT_NAME
 Where 
 coluse.TABLE_NAME = @TableName 
 And coluse.COLUMN_NAME = @ColumnName'


 Execute sp_executesql @Sql, N'@TableName nvarchar(128), 
 @ColumnName nvarchar(128), 
 @FK_TableName nvarchar(128) OUTPUT, 
 @FK_ColumnName nvarchar(128) OUTPUT,
 @ConstraintName nvarchar(128) OUTPUT', 
 @TableName=@TableName, 
 @ColumnName=@ColumnName, 
 @FK_TableName=@FK_TableName OUTPUT, 
 @FK_ColumnName=@FK_ColumnName OUTPUT,
 @ConstraintName=@ConstraintName OUTPUT
 
 if Len(@FK_TableName) > 0 And Len(@FK_ColumnName) > 0
 Begin

/* have found foreign key and the lookup table 
 so pick a random primary key from the lookup table */
 Set @Sql = 'Select top 1 @InsertValue = Cast(' + @FK_ColumnName + 
 ' as varchar(200)) From ' 
 + @FK_TableName + ' Order By newid()'
 Execute sp_executesql @Sql, N'@InsertValue nvarchar(128) OUTPUT', @InsertValue=@InsertValue OUTPUT

End
 Else if(Len(@ConstraintName) > 0)
 Begin
 
 /* OK we've found the foreign key constraint but have no idea what the
 lookup table is. This is because we are joining on a unique index to the 
 lookup table not a primary key. Make a MASSIVE assumption in this instance
 - that the lookup table has a link value of 1 */
 Set @InsertValue = '1'

End
 Else
 Begin
 -- no foreign key so the max that can be inserted is based on the datatype
 -- do not bother with any thing greater than thirty thousand - big enough
 if @DataType = 'bit'
 Set @MaxValue = 1
 else if @DataType = 'tinyint'
 Set @MaxValue = 255
 else if @DataType = 'smallint'
 Set @MaxValue = 32767
 else
 Set @MaxValue = 32767
 
 -- randomly generate a number to insert up to maximum
 Set @InsertValue = Cast(ROUND(((@MaxValue - 1) * RAND() + 1), 0) as varchar(200)) 
 
 End
 End -- end of numeric processing
 
 -- building up output string
 Declare @Delimiter char(1)
 if @FirstLoop = 1
 Begin
 Set @FirstLoop = 0
 Set @Delimiter = ''
 End
 Else
 Begin
 Set @Delimiter = ','
 End

Set @SqlOutputFields = @SqlOutputFields + @Delimiter + @ColumnName
 
 if @StringInsert = 1
 Begin
 Set @SqlOutputValues = @SqlOutputValues + @Delimiter + '''' + @InsertValue + ''''
 End
 Else
 Begin
 Set @SqlOutputValues = @SqlOutputValues + @Delimiter + @InsertValue 
 End

Fetch Next From procCursor Into @ColumnName, @DataType, @CharacterMaximumLength
 End -- finished this column = go to next

close procCursor
 deallocate procCursor

-- outputting the sql string
 Set @SqlOutputFields = @SqlOutputFields + ')'
 Set @SqlOutputValues = @SqlOutputValues + ')'

select @SqlOutputFields + ' ' + @SqlOutputValues

End 
Go

usp_RandomInsertGeneratorWrapper

It’s very easy to create a wrapper stored procedure to get multiple statements. One row of random data isn’t that much use – 1000 might be.

Create Procedure usp_RandomInsertGeneratorWrapper
(
 @TableName nvarchar(128),
 @NumberOfInserts int
)
As
Begin
 
 Set NoCount On

Declare @StatementCount int
 Set @StatementCount = 0

While @StatementCount < @NumberOfInserts
 Begin
 
 exec usp_RandomInsertGenerator @TableName
 Set @StatementCount = @StatementCount + 1
 End

End

Usage

To get an insert for one line of data

exec usp_RandomInsertGenerator ‘MyTableName’

To get multiple insert statements call the wrapper function

usp_RandomInsertGeneratorWrapper ‘MyTableName’, 1000

Notes and disclaimers

What it does

  • It outputs an insert T-SQL statement for any named table in the database.
  • Detects foreign keys and inserts and makes a random but valid link to the child table (this was the hard part)
  • Detect different data types and does the necessary SQL formatting
  • Accounts for max character length in string type fields
  • Accounts for identity columns
  • Detects nullable columns and leaves them null

What it doesn’t do

  • Doesn’t account for unique indexes
  • There are omitted datatypes (cursor, timestamp, binary, varbinary, image) that I’m not really interested in right now.

Hacks and shortcuts

Where the foreign key links are linked to a child table via a unique index rather than a primary key it assumes a foreign key of 1. The INFORMATION_SCHEMA views don’t include index information. So this would necessitate delving into the sys.index table. Not impossible but the proc was complicated enough as it was. I didn’t need it so I didn’t do it.

Assumptions

  1. Foreign keys are of a numeric type i.e. int, float et. al.
  2. Foreign key constraints exist on the database. It isn’t magic – it can’t work out the relationships if they don’t exist.

Room for improvement

Here’s some extensions that may be useful for individual projects

  1. Implement missing datatypes.
  2. Create custom inserts for known field names to give more realistic data for your application
  3. Randomise the character data properly.

So that’s it. Live it, love it and fiddle about with it a bit.

Useful links

The official documentation for the INFORMATION_SCHEMA used in this procedure is here
https://msdn.microsoft.com/en-us/library/ms186778.aspx

As ever the source code is on GitHub. There is nothing more than what is here but my WordPress code formatting plugin hasn’t made a great job of formatting the SQL code so the GitHub link might give clearer view of the SQL. Also, it seemed cruel not to give it a home on my demo repository with the rest of my code samples.
https://github.com/timbrownls20/Demo/blob/master/T-SQL/usp_RandomInsertGenerator.sql